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
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
