Hi all,

Have a question regarding a primary key, or how to best create one. I've done some homework on keys but am having a hard time applying it to my app. Any thoughts or ideas would be appreciated.
I've done a db for an ice cream shop (this is a real app and not homework BTW :) )
My question involves 3 tables (leaving non-pertinent fields out):

tblFlavor
pkFlavorID (autonumber)
strFlavorCode (3 char string - all unique) example = "133"
strFlavorName (50 char string) example = "Chocolate"

tblContainer
pkContainerID (autonumber)
strContainerCode (3 char string - all unique) example = "345"
strContainerSize (50 char string) example = "Pint"

this is the table in question and how it is currently designed

tblProduct
pkProductID (string 6 char - I concatenate strFlavorCode + strContainerCode to generate this pk via VBA) so "133345" would be Chocolate Pints

A few notes about the db:
-Using this for inventory currently. pkProductID's are on bar codes and scanned in/out of a freezer. (Currently working fine)
-db will be used in the future to generate P&L reports based on flavors of ice cream (costs x amount to produce x amount of this flavor)
- If I have to make major changes, now is way better time than later.

Questions:
Should I be using an autonumber as the pk in tblProduct or is that not necessary?
Could I be in for trouble down the road as it is now?
Would I run into any performance issues using a text field as the pk?
Should I eliminate the autonumbers in tblFlavor and tblContainer and use the text fields since they are unique?
Should I just shut my yapper and adapt it later because it works? (I expect it to grow much more complex in the next year or so).
I very much appreciate any thoughts.

Chris

The concatenated key in tblProduct is wrong. It is a violation of first normal form and it is preventing you from enforcing referential integrity.

tblFlavor
FlavorID (autonumber)
FlavorCode (3 char string - all unique) example = "133"
FlavorName (50 char string) example = "Chocolate"

tblContainer
ContainerID (autonumber)
ContainerCode (3 char string - all unique) example = "345"
ContainerSize (50 char string) example = "Pint"

tblProduct
ProductID (autonumber, pk)
FlavorID (fk to tblFlavor)
ContainerID (fk to tblContainer)

I removed the prefixes because conventionally, table columns are not prefixed. Be sure to enforce RI between tblProduct and tblFlavor and between tblProduct and tblContainer.

You should add unique indexes for FlavorCode and ContainerCode to prevent duplicates but the autonumber is a better choice as a pk. I would also add a unique index on FlavorID plus ContainerID in the product table. This is not a concatenated value. It is a two-field index. You'll need to open the index dialog. Name the index and select the first column. Choose the unique option. Then on the next line, leave the name column blank (this indicates that this line is a continuation) and choose the second field.

Questions:
Should I be using an autonumber as the pk in tblProduct or is that not necessary? Use an autonumber. The concatenated key will cause problems.
Could I be in for trouble down the road as it is now? Yes. You cannot enforce R and joining the tables for queries will be a real problem.
Would I run into any performance issues using a text field as the pk? Text vs. Number for primary keys isn't an issue unless you have hundreds of thousands of records.
Should I eliminate the autonumbers in tblFlavor and tblContainer and use the text fields since they are unique? I wouldn't. I would add unique indexes for the text fields though to prevent duplicates.
Should I just shut my yapper and adapt it later because it works? (I expect it to grow much more complex in the next year or so).The earlier you make your tables "correct", the less trouble you'll have later.

Thank you very much Pat,

Your suggestions make sense. I very much appreciate you taking the time to respond so thoroughly.

Chris