Primary Keys for Static Tables needed? (1 Viewer)

[Banana]

I have a couple of Tables that I use to look up, are static because they list counties, ethicity, and the likes. They are all smaller than than seven records. Those does not have any autonumber or have a primary keys enabled.

Doing some reading, it sounds like that those should have a PK anyway. Can soemone enlighten me whether it is really necessary and what are the benefits?

Thanks.

 

[Kelemit]

Banana, purists would argue that the static lists should have primary keys and that the data stored in your main table should be the primary key, and not the name of the country / state.

IE: (US Address example)

Main table:
Name, Address, State, Zip
Cheese, 1234 Somewhere, 14, 05034

As you can see, state is 14. When you run reports / queries, etc, you will need to link in the state table by PK then have it display the State name linked to that state PK.

Why? the benefit is that you employee table is smaller with only 2 digit numbers, etc and can be loaded / run faster. You do loose speed having to link the table and compare the numbers to return the state down the line.

I personally don't think its important and actually prefer not to use this method.

But others will argue otherwise, and most of the experts will tell me I'm wrong. You're gonna have to form you own opinion on this one, I believe, but the majority opinion, the "right way" to do what your describing is store the PK in the main table, and not the state name.

Kelemit

 

[Pat Hartman]

To be technically correct, each table must have a primary key. One disatvantage to not having a PK is that including the unkeyed table in a query will render that query non-updatable.

 

[The_Doc_Man]

For very small databases used by very small audiences, almost anything goes. (Pat, I DID say ALMOST.)

However, having a prime key on any table that has a natural relationship to another table is almost mandatory as a design principle. So for example if you have a state number and a multi-state business across seven states, you can store state number in a byte, which is 50% smaller than if you stored the literal abbreviation and a LOT smaller if you stored the names spelled out.

Another reason it is a good idea is that the Forms Wizards really like it if you have a prime key on a table that will be used in a Combo box or List box.

Not only that, if you have that prime key, you can define a relationship and ALL the wizards can see what they need to optimize your queries.

 

[Banana]

*bump*

Just had an opportunity to come across this situation that made me think twice.

Would anyone bother assigning a separate key column in certain lookup tables where the values would be numeric.

An example would be a table of weeks in a year. Since there are always 52 (53 in a leap year? Can't remember) weeks per a year, I can just assign it as Week #1. Using autonumber then is moot because the Week value would be in fact identical to 1 to 52. I can then designate this as a primary key.

Would that still work?

To take things a step further, suppose I have a table that lists hours for lookup and the value are Single;
0.05
0.5
1
1.5
2
....

Does that warrant an autonumber or can those be used as key themselves?

I can see how string variable would be lousy compared to an autonumber, but when we're talking integers and even single, autonumber seems to be moot.

Is that true?

 

[Pat Hartman]

I typically use a single table to hold all my small lookup tables so I use an autonumber PK but if you are using individual tables then there is no need to create artificial keys. I don't use natural keys if they have the potential to change though. So, just be careful of text strings that may change.