Primary Keys

[Guest]

I am just starting a new database. I have the same customer in multiple
locations, which can have multilple contacts at that one location. When
setting a primary key, should I do it by company or contact. Should I use
AutoNumber or set my own ID's.

 

[SusanV]

Hi Jackie,

Since one customer can have many locations and each location can have many
contacts, you should have a table for Customer, with a one-to-many
relationship to another table for Locations, which will have a one-to-many
relationship with yet another table for Contacts.

tblCustomers
fldCustID PK
fldCustomerName
etc

tblLocations
fldLocationID PK
fldCustomer FK to tblCustomers
etc

tblContacts
fldContactID PK
fldLocation FK to tblLocations
etc

As far as Autonumber vs your own IDs for Primary Keys goes, it doesn't
matter to Access so long as this field will be UNIQUE to each record - no
duplications. If you're certain your IDs will have no duplications, your own
ID may be easier for you to work with for the Customer ID, but I would use
an autonumber for the primary key on the child tables (locations and
contacts)