Calling all Microsoft Access Nuts!!!

[Audiomechanic]

I am an electronics tech for a drug store chain and I am trying to create a data base of store information and equipment rollout information for all the stores that I service. It's been about 3 years since I have used MS Access so I'm a bit rusty. here's how I have the tables setup so far:

I have what I call the Main Table. It's where all of the store data goes. Store numbers, addresses phone numbers, etc. In this table, I have several columns setup with yes/no cells pertaining to the completion of equipment rollouts. I have several other tables setup for each of the equipment rollouts that go further into detail about the rollouts than the columns in the main table do.

Now, all of the tables are linked by the store number (the primary keys of each table). Here's my question: Lets say I add a store to the main table, how do I make the store number columns in all "sub tables" auto update with the main one? In other words, I only want to create one record for the store, not 3 or 4 of them for all of the tables. This means, if I create a record in the main table, a record will also be created in all linked sub tables. If I change a store number in an existing record of the main table, the same record will also be changed in all sub tables.

Please Lord let there be some MS Access junkies on this board! Thanx ya'll for reading (those of you who got this far without falling asleep! )!!!!

 

[WeakButHopeful]

Hi Audiomechanic, I'm not so sure your question is specific to MS Access as it is a general relational database design question. I'm going to tell you what they enforce where I work, and if you feel it's too rigid then check around with others. I'm just trying to give you best possible advice, but others might have short-cuts or have better understood your question.

I don't know if you've ever heard of 1st, 2nd, 3rd and 4th normal form (as in "database normalization") but you are, uhmmmm, kind of breaking some design rules (I think). Here's a link to what normalization is about, picked from a Google search: http://databases.about.com/od/specificproducts/a/normalization.htm

For instance, a "main table" like you describe is usually a bad idea, because (in your example) a single store may have several phones (manager, back office, fax, etc.) which should be in a separate table. Also, yes/no values in that main table pertaining to equipment rollouts could fall out of sync with the separate equipment rollout tables. A basic relational design rule is to never store the same thing in two places (OK, yes, there are exceptions, called an intentional denormalization, but that's abnormal), because there's one thing you can be sure of...it won't be the same thing. What's usually done is that the "queries" dynamically capture the situation when it is needed. In other words, what stores have had equipment X rolled out to them, and when? Then you would query: "SELECT STORE_NAME, ROLL_OUT_DATE FROM STORES INNER JOIN EQUIP_ROLLOUTS ON STORE_NAME.STORE_NUM = EQUIP_ROLLOUTS.STORE_NUM WHERE EQUIP_ROLLOUTS.EQUIP_NUM = 'X';" You see, there's no way for the database to get out of sync. Stores had rollouts if they have matching rows in the rollout tables, and did not if they don't...no messy switches.

As for your question on the addition of stores, or store numbers changing, I think we might want to revisit that after you consider normalization. Perhaps some of the "extra rows" for each store were things you thought you had to do. Relational databases have the concept of NULL values which can be checked for in queries. So you are not forced to store extra rows in other tables when creating stores, only to consider what you want done in certain queries if certain data is not available. For instance, can a store that has been built but is not open to the public have equipment rolled-out to it? If so then there might not be rows in the "STORE_HOURS" table, or a value in the "DATE_OPENED" column (I'm just trying to think of some example related to your situation).

Of course, as you say the various tables are related through keys...primary keys and foreign keys. Here's a tutorial I found in Google: http://www.bcschools.net/staff/AccessHelp.htm
A foreign key is a column in one table that is the primary key of another table...thus establishing a relation between the two tables. A table may have multiple foreign keys and so be related to multiple other tables. I believe in Access you can drag a relationship visually between the two tables.

Finally, you might not want to use the store number as you primary STORE table key, since, as you point out, this would have affects on the database if the store numbers changed. In my experience important, top of hierarchy tables are given unique identifying numbers when they are created (some database systems have the concept of IDENTITY columns which AUTO INCREMENT to provide a unique value, others use GUIDs). The "human" store number then just becomes a data column which is kept ONLY in the STORE table...it does not need to be anywhere else. "STORE 27" means as much to the computer as "MAPLE BOULEVARD STORE"...internally that store row's primary key might be 8, and every associated table will have STORE_ID of 8. Make sense?

I hope I helped. If not I hope someone else does. My heart was in the right place.

 

[Audiomechanic]

well, I DID say I was rusty.

Interesting concepts in that post that I am considering. Like the ID column. It's been so long since I have used Access (which is the only databasing program I have experience with) I'm pretty much having to re-learn it.

So, Information such as store phone number and IP addresses and all that should be stored in separate tables? Do these tables only have two columns a piece...say the ID column (primary key) and then the info column (phone number or store number or IP address or physical address, etc)? That would create a lot of tables! What about rollout tables? Are they individual tables per column as well? Perhaps my thinking is incorrect (actually I'm pretty sure it is...I'm by no means an expert at databasing). So when an equipment rollout is updated, instead of everything linked by store number, it will be linked by ID number as the primary key and then everything will be organized by query? Interesting. I will have to play around with this.

Thanks for the help and the links! Very long and detailed reply, thank you for your time!!!

EDIT: Ok, from what I gather, store info like store number, address, etc. is stored in one table with a primary key of an ID column using an AutoInteger. The rest of the tables for equipment rollouts and employee performance tracking and such will not have any repeated data with the exception of the same ID column which will be the relatioship between all tables. I think I'm starting to get it! I'll be in the shop all day working on this and will check here every now and then for replies. Thanks again!

 

[Audiomechanic]

Ok, I think things are beginning to get organized. I already have a few queries created that present to data that I want in an organized fashion. Now I have another question.

The company that I work for uses work orders (known hereafter as Tickets) that he store opens for problems that need to be fixed. Each Ticket has it's own specific number that is unique to the ticket itself. We service 233 stores and each store opens an average of about 1 ticket a week. This is what I want to do:

I want to be able to enter a ticket into the system matched to the store number it is opened from and enter in what problem the ticket is for. In addition, I want to be able to enter is what tech went to the store or that particular ticket number and whether or not it was fixed.

I am tracking employee performance with this. What techs are closing what tickets and what techs are not. Also, what stores have repeat problems after certain techs visit the stores.

How would I set this up? One table per ticket to all stores? One table for each store for all tickets to that store (that would be a LOT of tables!!!!! 233 of them to be exact!!!)? This is a tuffy.

 

[Audiomechanic]

Ok, I'm confused again.

I have the store info table setup with an ID column as the primary key. The rollout tables will be referenced to that column. The number in it is auto generating. Here's the thing though, what happens when I have to add another store? We open stores at a rate of about 5 per year but we are beginning to recycle old store numbers! For example: store #3286 has ID 24 and is an old store that was built about 15 years ago. Store #3324 is a brand new store but will have ID 234! We base everything off of store numbers. That is how we reference everything (aside from this database). Will this throw us off?

Also, I'm still not sure why the store number cannot be the primary key. We base everything store related (which will be everything in this database) on the store number, the store numbers never change and there are no two alike. I know I said they change before but that was hypothetical, they really don't change.

 

[WeakButHopeful]

Hello again audiomechanic. Sorry, I don't have access to CF at work and so I just saw your posts after getting home from work and eating dinner.

First, I did not mean to imply that everything should be in separate tables...I was just trying to steer you away from having everything lumped in one "main" table. Generalizations can be dangerous, but I think it's fair to say that a column can be in a table if it has only one purpose (no hair color or favorite sock color columns), and if it is in a one to one relationship with the primary key (you wouldn't have 3 columns for phone number, because what if they have only 1, or 4 or more phones).

In your example "address" could probably be in the main table since a store has just one address, but in a retail sales situation customers often have multiple addresses (work, home, vacation, etc.). Now if you want to get fancy you can have what's called a "joiner" table to relate separate store and equipment tables. So if you have 15 stores and 20 pieces of equipment, the third, joiner table would have (for the sake of example) 3 columns...the first two columns would be the foreign keys over to the store and equipment tables, and the third column would be the date rolled-out to that store. Do you start to see how things are kept together only when they belong together, and are separated both for clarity and also to allow one to many OR many to many relationships? A database design course often asks the students to identify the real world things that are actually separate, and what goes on with those things, BEFORE thinking about the database at all.

I think this idea of joiner might show you some ideas for your ticket question. You've got stores, equipment, employees, tickets, employee-work-on-tickets. Let me think out loud so you can see my assumptions (some may be wrong in your case). OK, a ticket is going to be for a particular store? (hmmm...or could a ticket be opened for all downtown stores? I guess not). So the tickets table could point back to the store table as a foreign key, but it's primary key could be the ticket number itself (comes from a paper doc huh? doubt that will change?) Should the ticket table have a foreign key to the equipment table, or is that just for roll-outs, not trouble tickets? Or, could there be more than one piece of equipment related to a ticket (doubt it, but if you wanted to allow for that then you'd do a joiner table). I assume the ticket table would also have a date opened, priority, contact indvidiual (or maybe that could be a foreign key to the employee table?) The employee-work-on-tickets table would obviously foreign key to the employee table and foreign key to the ticket table. The primary key could be a combination of those two foreign keys plus a date and time they did the work. Another data column might be the disposition of their work (an activity code or something).

I'm not saying you can't use store number as primary key, but as you point out: #1 - it is going to be the foreign key to a bunch of other tables, #2 - they already reuse store numbers. It seems to me that having the tables tied together unambiguously is a "good thing", and nobody says that internal ID will ever be shown on any report. But the question of how a store number itself can be ambiguous to a human being is not a computer issue. When the human sees a report produced by your new database how do they know whether you mean the old or the new store 5555? Maybe you just need a programming discipline that says you never show a store number alone on a report, always directly next to the store name, to avoid confusion. Just a thought.

And, of course, since this is the 21st century I have to give the standard warning that my comments are meant only as explanatory for how relational databases are usually designed, not as advice on what you should do at your job. (Sorry, you now how it is).

But I hope these comments and/or the links have helped you get un-rusty with regard to Access. Sorry our schedules don't seem to mesh too well! I'll look for any follow-up questions late tonight or (failing that) tomorrow after I get home from work.

 

[Audiomechanic]

You are a fountain of knowledge, my friend! Despite the disclaimer.

I am beginnning to see how things come together. "It's all coming back to me now."

I do not disagree with using an ID column as the primary key, the only poblem I see is that since we base everything, our whole organizational structure, on store numbers, it would be easier to setup tables and such using store number 3286, rather than it's ID which may be 45. Yes, I can ommitt the ID from any reports and already have a few setup with that column ommitted. I can, however see your point of normalization. With each table being based upon store number, that would create a lot of redundant coumns with repeat information. But hen again, isn't he ID column being repeated as well? How then can I base this information without being redundant in all these tables? This is my quandry.

You are a God send! Thank you for the help!!!

 

[WeakButHopeful]

Hi A.m. You're welcome.

To answer your last question authoritatively I'd have to know a bit more than I do. I work with relational databases all day, but some things you just don't question if 100% of everything you see looks that way...you just assume it's correct. As a matter of fact, at my job they use the Erwin data modeling tool which graphically designs the databases and spits out the DDL to run the tables up. So yes, foreign key columns are by their nature redundant in the sense that they exist in the "parent" table and the "child" table. However, they allow tables to be tied together using "referential integrity" so that you don't end up with "orphans" (e.g., tickets for store numbers that don't exist). You can also set-up "cascading delete" so that when the parent is dropped its direct children will be also.

Now, all of the above is nice but I know I am still begging the question a bit. I assume you know you can do Queries with Inner Joins so that even if you're creating a report on a table that does not have store number, you can get it from the STORE table through a join (if you didn't know that then maybe that's where we're miscommunicating). And as you say Store number would not be more or less redundant than Store ID. In my mind the tie breaker on what to use as your key is that (I believe) with Referential Integrity turned on Access won't let you change the parent's key if there are children...so if you ever need to change Store Number, you couldn't if you used it as your keys. And, of course, you said they reuse store numbers and you don't want to pick a primary key if your business model can have dups.

I hope this was of some help. Ciao for now!

 

[Audiomechanic]

Hmm, I see your point. The more I work in this the clearer it becomes. See, the company I work for has currently a little over 5000 stores open nationwide. Now, my market only covers 233 of those stores so the repeat numbers are more than likely not from closed stores in our area. Even though the numbers are repeating, they are most likely new to us. Also, you asked in a previous post how we tell whether or not a store is new, well, we just kinda have to know. I doubt that the database could tell me any info on that other than different types of equipment (such as store 2757 has a really old phone system cause it's a really old store but store 2848 has the newest style caused it opened last month).

I have a quople more questions but I'll save them till the morn. Too tired to continue typing with correct grammar and such.

Thanks again!!!! I can't tell you what a help you're being in this project.