help with designing a new table and which keys to use

[dd*]

A Database design question
I need help with designing a new table and which keys to use. I'll do my best to explain what I want.

I have an existing table named tableTitle
with the fields
key ITNum titleName VersionNumber

I have an existing table named tableBill
with the fields
key itNum billName BillNumber

On a form
Users will look up the tableTitle and select a titleName.
Users will look up the table tableBill and select a billname.

The user selected entries are to be stored in the new table named TitleVersion.
TitleName is the main field to contain relevant field data.

Note the TitleName and its VersionNumber will be unique.

In the new table do I create the keys as foreign keys
or is there another way to design the new table.

see attached screen capture

 

[The_Doc_Man]

It would be better in the long run that the field you named "KEY" is named more in line with the table's purpose. For example, if I have a Persons table, its key would be PersID or something similar. This is because it is considered good practice that a key field name have some mnemonic value such that when you see the field name you instantly recognize what it is and where it originated.

To select a key and to select an index are two different things.

If you have a table for which a given field (that isn't the key) will be searched very frequently, you can create an Index on that field, even if it isn't unique. Access will make it easier to look up what you wanted. Don't go overboard with indexes, but you can have more than 1 index for a table. I once had an app that needed six keys on one particular table. It was UGLY - but it worked.

However, if you are going to designate a prime key, your candidate field must be a unique value. If you have a table of employees, your company's internal employee ID is likely to be a candidate key. The employee's last name is almost certainly NOT a candidate key (because you can have two employees named Smith, for example). If you have multiple candidates and they are unique, it is better to declare the shorter key as the prime key as long as there is no chance that you would ever want to edit that key. The reason for the shorter key is that Access keeps a list of prime key values along with pointers to where in the table they are located. Shorter key values means you can fit more entries into a disk block. If there is even a slim chance in Hell that the candidate key could be changed, that key is disqualified (Exception: It can be changed IF and ONLY IF you are changing ALL of the keys at once due to some change in the software.)

To read more on this topic, search the forum for "Synthetic vs. Natural Keys" because there are threads under that topic where we beat the whole subject to death and left behind a bloody pulp. (We get like that sometimes.)

 

[dd*]

Thank you Doc_man for your response, and will do on your suggestion to search the forum