Chubby - Some interesting question to which there are no hard and fast answers.
But here are some guidelines gathered from the field of battle over 10+ years of developing robust, reliable Access database designs since version 1 was released in 1991.
1) It is quite reasonable to allow the user to enter the primary key for a record. Indeed you'll find many experienced people on these boards that will INSIST that if you can calculate the Primary key automatically ( like using an AutoNumber ) then it is NOT a good primary key.
However, if you let the user add the data then you must add some code to cope with telling the user that they have entered a duplicate primary key when that eventuality does occur ( Oh and it will .. .. )
2) I have databases with 100+ tables and very, very few of them lack a Primary Key of some sort.
Remember - the primary key is the only way that you have of UNIQUELY identifying any one record from any other and it is the tool that you use to prevent the same data being entered twice ( or more ).
3) I don't know what you mean by "Master and Child primary key".
However, on the issue of naming and creating relationships :-
Lets say we have an employee database.
First table is the tblEmployee - ( start every table name with tbl )
Primary Key will be the staff number that HR allocate when they join the company. I resist the temptation to call the field in the table StaffNumber - instead calling it EmployeeId. Because I have a single field Primary Key - I try to stick to the convention of Primary key name = table name minus "tbl" at front, plus "Id" at the end.
Other fields as appropriate.
Note: Staff Number cannmot be automatically generated because HR 'invent' it. ( Some HR depts use the person's NI number ) So it has to be loaded manually.
Now lets say we have some equipment that gets allocated to individual employees. Store this in tblEquipment.
More than one piece of kit can get allocated to an employee so we need a One-to-Many relationship structure.
Primary Key is EquipmentId and may be a text field that holds the serial number of the piece of kit. ( Again user entered - because it cannot be calculated. )
Another field in tblEquipment needs to be the Foreign Key that points to the tblEmployee to define who the piece of kit is allocated to.
So add field EmployeeRef of exactly the same type and size as EmployeeId in tblEmployee.
Note: PrimaryKeys end in "Id", Foreign keys have the same core name but ending in "Ref". This make debuging code and things FAR easier as you progress into more complex databases, becuase you instantly know which end of the relationship link you are dealing with.
( If Prime Key is an AutoNumber then Foreign Key needs to be Long Integer )
Next table is tblTraining
PrimeKey = TrainingId
( other fields as required )
Now we have the problem that many employees go on a single course and employees go on many courses. So we need a many-to-many relationship - which Access does not support.
So we create what is called a linking table
tblEmployeeTraining made up of two fields.
EmployeeRef
TrainingRef
As these are both "Ref" fields we know the data type and size that they must be and the fact that they are Foreign keys pointing to the tblEmployee and tblTraining respectively.
Now make BOTH of these fields the joint Primary Key for this table.
Now you can allocate lots of employees to a course by adding records with employeeId values into EmployeeRef and the same TrainingId value into TrainingRef.
To put the same employee on lots of courses you create more records with the same EmployeeId value in each EmployeeRef field and the different traingin course ID values in TrainingRef.
If you ever try to allocate the same employee to the same course more than once THEN you get a Primary Key duplication error - so Fred can't get booked on to two seats on the Access Introduction course on the same day!
If an employee cancels their booking then just delete that one record from the table.
In general Primary Keys should be the field ( or fields ) that uniquely identify the record. If all else fails - resort to Autonumbers - ( I know some 'purists' wouldn't agree ). Main rule is think about it carefully.
I'd feel very uncomfortable if I had two primary keys of the same name because I can forsee horrible debugging problems arising from it. My personal naming convention ( above ) prevents that problem from arising.
( As an aside - also see the FAQ on this board about Naming conventions of Access objects and why you should aavoid the space character appearing in ANY name that you ever give anything in Access )
{ The same is actually true for any Windows App. }
This has turned out longer than I originally intended.
I hope it answers your questions and please come back with more are you come across 'challenges' in your designs.
G LS
)