Primary Keys and auto number

[gingette]

Hi there.

I am creating a database and i have the layout I want. although I am having problems as i am using autonumber as the primary key in each of the tables. Is there are way to make this work as the data to be entered into this database does not have a natural key until they are 4 months into the process.

i have attached my relationships diagram.

if it is not a good idea to use an auto number as the primary key does anyone have any suggestions as to what i should use?

 

[Cosmos75]

It is a good idea to use an autonumber as your primary key. Your primary key should be unique and most often meaningless. Unless you have a natural key such as a social security number or some other value that is unique to each record.

I can't tell from your diagram what your relationships are, which direction the one-to-many relationship goes. Also, you may want to rename your primary key fields for each table, e.g. StudentID, FinanceID, UCAS_ID. That's my personal preference so that it's easier to identify primary keys in queries, but someone else may show me the error of my ways.

 

[neileg]

Having seen your structure, I can see that the StudentFinance table is not normalised. The three installments should be recorded as 3 records in a new table, not three sets of fields in this one.

On your primary key question, each table needs its own primary key. You link the tables by having a field in the child table that holds the primary key from the parent table. If the parent PK is an autonumber, then this field (known as a foreign key) will be a long integer. The child table's own PK can be an autonumber too, but this is not synchronised with the autonumber in the parent table.