Lookup Table "Best Practices" 2

[Griphus]

Hi!

A coworker and I are having a debate about the most correct way to do something. I'll attempt to describe this as objectively as possible.

We have a database with several lookup tables. This is for building motors, so the tables are for things like bearings, the enclosure for the motor, customer names, etc. So, let's use the bearings as an example.

In one table we have a field describing the type of bearing used (whether it should be an ID# or not is part of the debate). In another table we have a list of the different types of bearings to be used as a lookup on a form.

Question 1: In the lookup table, should the type of bearing be the primary key, or should there be an autonumber? One would be a text, the other obviously a number.
Question 2: In the database, should there be a formal (one-to-many) relationship between the bearing lookup table and the motor information table, or should the lookup table just be bound to a combo or drop box in a form with no relationship defined?

Hopefully I asked those in an unbiased way.

Feel free to ask if there are any clarifying questions needed.

Thank you!
Timothy

 

[dhookom]

I almost always create an autonumber primary key for every table. I have never regretted doing this. It has always served me well. IMO, you should create the relationships.

Duane
Hook'D on Access
MS Access MVP

 

[Griphus]

At the risk of being too audacious, a "why" for any answers / opinions would also be awesome so we can educate ourselves. Teach a man to fish and all that...

 

[dhookom]

As I stated, I have never regretted doing this.
1. The autonumber primary keys are a little faster than possibly text fields.
2. I don't have to ever worry about primary key values changing
3. If you set this up correct, the autonumbers are always unique
4. I never ever have to think about primary keys
5. This doesn't prohibit you from creating unique indexes on other fields
6. Autonumbers upsize to SQL Server well

If you create the relationships, this is internal documentation and assists when creating queries.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I agree with Duane in that you can never go wrong. For novice users this is the best and safest approach.

Now, with that said, I often do not do this. But only after understanding all possible ramifications.

So there are two questions
1) When can you simply store data in a table from a lookup table instead of saving a key and relating it to the lookup
2) When do you need to add an artificial key like a autonumber instead of using a natural key

1) It is fine to simply store data, but you need to be careful and think it through.
Without a relationship you will not have cascades updates or deletes or enforce referential integrity.

Need to ensure that the value in the lookup will not change to represent the same entity

Example you have a table of colors Red, Green, Yellow, Blue
The color red is will always be represented by the word red. In the future the color red will not be represented by some other word.

However, if this was a table of departments within you work :Systems Engineering, Process Improvement, Public Relations. You may find that the new boss wants to call the department formally known as "Public Relations" as "Strategic Vision". It represents the same entity. If this is data stored in several data tables then you will have to run update queries to fix this. If you would have stored a foreign key you could simply change the description in the lookup table.

2) Especially for lookups an artificial autonumber key is not necessary if the key is a good natural key. But again this takes thought. For me I will only use a natural key that is a short single word or integer number without any special characters (#'"*?). Although multiple words will work I prefer to limit to single words.

So if I have a table of Animal Types

Mammal
Reptile
Bird
Fish

That would be fine primary key no need to add an autonumber. Especially if it is a lookup defined by the developer and the user is not adding to the list. If the user is adding to the list then I am sticking with the autonumber because you have no idea what they may do. If they put a ' or # in the name it could cause some serious problems with queries.

The nice thing about a natural primary key is that as a foreign key it is also data. Assume my animal type lookup table has several other fields. When I stick Mammal into my data table as a foreign key you can see the word Mammal in the data table without relating it to the lookup in a query. But you still can link on this value and show the other fields in the lookup table if needed.

Question 1: In the lookup table, should the type of bearing be the primary key, or should there be an autonumber? One would be a text, the other obviously a number.

Without knowing all the possible type names I cannot tell you. Yes you will always be safe with an autonumber.
Do the bearing type make a good natural key? Single text string, no spaces, no special characters? Are the names never going to change
Looking at these types you could get away with it but I would not be comfortable with the multi word keys and dashes in names

http://www.skf.com/portal/skf/home/products?newlink=1_0_1b&lang=en&maincatalogue=1

So I would make all my primary key single words without special characters
Deep groove ball bearings becomes Deep_groove_ball_bearings
Y-bearings becomes Y_Bearings

so my lookup table would then be
BearingTypeID (Y_Bearing)
BearingTypeDescription (Y-Bearing)

But if you go to that length you do not save much over going with a autonumber key except that you can read the foreign key easily.

Question 2: In the database, should there be a formal (one-to-many) relationship between the bearing lookup table and the motor information table, or should the lookup table just be bound to a combo or drop box in a form with no relationship defined?

I would say no. A lookup table relationship does not normally enforce referential integrity, and need or desire cascade deletes and updates.

The only reason to define the relationship is to ensure referential integrity and allow for cascade deletes and updates. If you cannot create a data record without a bearing type foreign key then you may want to ensure referential integrity. If you would delete a type from the lookup would you want all records that have that type to be deleted? No probably not.

 

[Griphus]

Thank you both for the answers. Much appreciated.

 

[spartansFC]

Hi

I was just about to ask a question about lookup tables and there's already one been asked and answered. The reason i was going to ask is i do use lookup tables similar to the way Griphus uses them, but according to

http://access.mvps.org/access/tencommandments.htm,

lookup up tables are bad and should never be used which got me confused.

I guess it's what you use them for or do they slow up the database in some way, form loading, queries etc.

Does Duane and MajP generally use lookup tables in their databases?

Mikie

 

[dhookom]

Mikie,
There is a difference between lookup tables and lookup fields (defined in tables). Using lookup tables is commonplace. The 10 commandments page refers only to lookup fields which I never use. I monitor lots of news groups and fora and see questions from people who use them and are confused.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

I think i get what you mean, if we use an example say car makes:

tblPerson
PID
forename
surname
CarID

tblCars
CarID
CarType

within tblCars you would have BMW, VW etc....

i would never normally do a lookup in tblPerson, i would create the form using tblPerson, then create a combo box on tblPerson.CarID with the rowsource "SELECT CarID, CarType from tblCars.... ", then if we looked at the tblPerson it would just have a numberical value for CarID, this is the way i think you've mentioned doing it before in one of my previous questions.

So is that the right way?

You're right, i was getting confused between lookup fields and lookup tables but i think i've got it now.

thanks

 

[dhookom]

SpartansFC,
You are correct regarding the lookup fields vs lookup tables.

Duane
Hook'D on Access
MS Access MVP