If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup Table design question
If I have a lookup table consisting of a single field, should I need to have
an autonumber primary key in addition to that single field? Currently I have only the one field, set as the primary key, with referential integrity to the main table so if the field changes then it updates the main table. Seems to work fine In this situation, what is the advantage of the autonumber primary key? Thanks Joe |
#2
|
|||
|
|||
if all the values in the lookup table are inherently unique, then you have a
"natural" primary key and probably don't need a "surrogate" primary key such as an autonumber field. keep in mind that when a primary key is a value that can and may be changed, then not only does referential integrity need to be enforced in the tables' relationship, but also Cascade Updates needs to be enabled. also, if you change an existing value in the lookup table at the same time that other users are entering data in a table that uses that value, then the cascade update may cause updating conflicts. "Joe Williams" wrote in message ... If I have a lookup table consisting of a single field, should I need to have an autonumber primary key in addition to that single field? Currently I have only the one field, set as the primary key, with referential integrity to the main table so if the field changes then it updates the main table. Seems to work fine In this situation, what is the advantage of the autonumber primary key? Thanks Joe |
#3
|
|||
|
|||
On Tue, 30 Nov 2004 22:42:12 -0500, "Joe Williams"
wrote: If I have a lookup table consisting of a single field, should I need to have an autonumber primary key in addition to that single field? No. Currently I have only the one field, set as the primary key, with referential integrity to the main table so if the field changes then it updates the main table. Seems to work fine Yep. In this situation, what is the advantage of the autonumber primary key? The *only* advantage is if you want to - rapidly - be able to change a lookup value in the lookup table and have that value instantly change in all records where it appears. With the two-field table the text only appears in the lookup table so you only need to change it there. If you use the one-field table, you must set Cascade Updates and it will update every linked record if you change the field; this can be timeconsuming and will contribute to database bloat. If it won't often happen, don't worry about it. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#4
|
|||
|
|||
"Joe Williams" wrote in
: If I have a lookup table consisting of a single field, should I need to have an autonumber primary key in addition to that single field? Scenario one: LookUpColours ColourID* Description ======== --------------------------------------------------- 0001 A deep tangy red, with hints of fire and warmth 0001 A hard scarlet, the colour of fresh blood and steel 0003 Delicate pink, like rare venison or new salmon.. etc etc Advantages: easy to change and update descriptions; no risk of user error due to misspelling; pretty fast joins on numeric keys; you can use the numbers to create a custom sort order... Scenario two: LookUpColours ColourCode* ===== Red Green Blue Black Yello Advantages: since the value stored in the child table (i.e. the FK) is english-readable, you don't have to make a join query to find out what colour things are; when required, joins are reasonably fast and efficient on _short_ text strings; the DB engine won't let you misspell a FK value anwyay; So: you have to take your pick based on the needs of your database. I will often use #2 when the description is short and pithy, like simple colours, or Ready/ InUse/ Finished or whatever, particularly if I can mangle the words so they appear in alphabetic order. For really static lists (like Male/ Female/ Uncertain) I might even leave it buried in the ValidationRule but I have to be sure it's never going to change. Just another 2-p worth! All the best Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Table design | BillT | New Users | 11 | May 25th, 2004 03:41 PM |