Why use Autonumber as the primary key? 1

[Quintios]

I have a lookup table that I use to force users to pick the value for a field from a list. Let's say that it's a list of fruit, and the table has the field 'FavoriteFruit'.

Seeing as how my list of fruits are all unique (there won't be two choices that contain the exact same values), then why bother creating a separate index for the list?

Why can't the table be like this:

tblFruitList
---------
FruitName (primary key)
---------
Apple
Banana
Kiwi
Orange


As opposed to this:

tblFruitList
------------
FruitID(PK) FruitName
------- ---------
1 Apple
2 Banana
3 Kiwi
4 Orange

It seems to create difficulities when displaying table data in a form, as the information in the table is a number, not a name.

Please comment.

Thanks in advance. Onwards,

Q-

 

[930driver]

The main idea here is so that the data is stored only in one place.

If you have the word Bannana stored in every record as someone's favorite fruit, later when you realize that you mis-spelled it you only need to change the spelling in one place, the look up table. But if you use your system, if you ever need to change the spelling to Banana, you'll have to find every single occurance and change them all.

A better, more real-world example, would be a phone number area code or zip code changing.

You should do a little studying on 'Normalization' and it should become clearer to you.

Now, on your other comment about display troubles: On a form you should be using a combo box to display the choices and then Banana will show up just fine. On reports and queries you add the fruit table to the query, joined on FruitID (or whatever the key relationship is) and display the fruit name instead of the key. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[930driver]

Oh, hi Q,

I just banged all that in, not paying attention to whom I was writing!
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Quintios]

Oh dear, I think we're talking about different things again.

What I'm saying is that yes, use a lookup table, referential integrity will allow me to update all records if I spell a fruit wrong. What I don't understand is whether or not to create a lookup table with a separate primary key, say the autonumber, as opposed to the actual fruit name.

Shouldn't you be working on fixing that database I sent you? ;-) just kidding...

Onwards,

Q-

 

[Maquis]

I use autonumbers primarily to save space in the database.

Let's say this is your fruit table:
1 Apricot
2 Banana
3 Navel Oranges
4 California Oranges
5 Macintosh Apples
6 Red Delicious Apples

Now, if you have...oh about 10,000...users picking favorite fruits. I would much rather have "6" stored 10,000 times in my user table than "Red Delicious Apples".
Maq
<insert witty signature here>

 

[Quintios]

Maquis, that does make some sense...

I guess I'm having a bit of trouble displaying the data. If a table has a number instead of the text, how do I get the text to show up in a form instead of the number that represents the text? EG, how do I get &quot;Red Delicious Apples&quot; to show up in a form, as well as allowing the enduser to select a different fruit from a combo box, rather than having the number &quot;6&quot; sitting there? I'm trying to mess with the 'Control Source', but I'm not getting anywhere...

Thanks! Onwards,

Q-

 

[Maquis]

If you have the fruit displayed in a textbox on the form then the easiest solution is just to base the form on a query which includes your main table and the fruity lookup table. The query should include all the necessary fields from your main table + the fruit name from the fruit table. A simple join on the fruit id will work.

For the combo box, have 2 columns in the combo box. The first column should contain the fruit id and the 2nd has the fruit name. Make sure the bound column is the 1st column (so that the fruit id is stored in your table) and you can set the 1st column's width to 0 so the only thing showing is the fruit name. (If you prefer, the combo box wizard will help you set it up this way by default) Maq
<insert witty signature here>

 

[Quintios]

Maq, you are the man.

I'll fix it up good now!

Q-
Onwards,

Q-

 

[WildHare]

[930Driver wrote:]
The main idea here is so that the data is stored only in one place.

If you have the word Bannana stored in every record as someone's favorite fruit, later when you realize that you mis-spelled it you only need to change the spelling in one place, the look up table. But if you use your system, if you ever need to change the spelling to Banana, you'll have to find every single occurance and change them all.


Well, actually, no. As long as the Fruit Name is the primary key of the lookup table, and is linked to the Person table with Cascade Updates set, R/I will update everyone's &quot;Banana&quot; with &quot;BananaRama&quot; if you change it in the Fruit Table.

Try it and see.

In this case, with a certainty that the text values will not be repeated, there is no compelling reason to &quot;autonumber&quot; key the Fruit table. In fact, I can very rarely ever find a compelling reason to autonumber key anything.



Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[Craig0201]

Jim,

The reason is space. A long integer takes far less space in a production db than text. Why autonumber? Well, if you don't need to guarantee consecutive numbers, why reinvent the wheel?

Craig

 

[WildHare]

Your point has merit - but to tell you the truth, with 60GB Drives retailing at less than $100, I'm not too concerned with saving space over speed and ease of development.

Example:
Long int - 2 bytes each
Text - oh, say 25 characters.

50,000 records = 23 bytes * 50000 = 1.5mb

60GB Drive @ $100.00 - the extra 1.5 MB costs about 40 cents.

It depends on situational characteristics, I suppose. But there are times when I've just used the values as the keys in lookups, and I've not had any complaints about wasted space.

Especially in the situation the original poster described, having just the list of text names makes it easier to do the combo-box pickers from other tables - no need to worry about hiding the numeric key, or that stuff. Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[Craig0201]

Agreed....

But every enterprise solution uses this idea.....it also relates to if there is multiple details attached to a key.....so I can refer to any column based upon the key.....

Technically, it is the correct way to go, but rules are there to be broken as long as you understand them and how to break them safely.....

Craig

 

[Quintios]

What I arrived at was this, if I need to use an Option Group of some sort, I'll use index numbers because it's easier (but not necessary) to correlate back to the supplying table. If no option group is necessary, then the actual unique name will suffice. Disk space is not an issue, but CPU time and memory space might be, and using the extra tables in the queries *might* introduce a *slight* amount of extra load.

Actually, that's another question in itself. If you don't use an integer number in a table which is used for an option group, how will the selected radio button respond with changes in table order? If the table is sorted ascending or descending, will the option group be tracking the wrong record/field?


Very very slight... Depends on how many records and such I would assume, but then I am definitely not a db optimization expert.

Onwards,

Q-

 

[WildHare]

As long as you understand that just setting an autonumber field as the primary key does NOT enforce uniqueness of the record, you'll be ok. Its just that I've seen too many cases where someone thought that just because they had an autonumber primary key, all was hunky dory in their table - until I showed them their content-duplicate records.

AutoNumber keying is totally useless, in my estimation.



Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[Quintios]

Excellent point. I'll be sure to set the second column as having to have unique entries.

Thanks!

Onwards,

Q-