IS IT OKAY TO HAVE THE USER ENTER THE PRIMARY KEY? 1

[chubby]

I've been doing some studying on Primary Keys, table structure, relational databases and I have a few questions.

1. Is it okay to let the endUsers enter the primary key?
Or is it better to setup the auto generator?

2. Is it better to have a primary key in every table like I have my 22 tables now? Or not?

3. All of the examples I read about the Master and Child primary key are different names or connected differently. My question is, all of my primary keys are the same type and name for all 22 tables is that okay?

I can't afford to lose any more data...

 

[dawnd3]

Hi there,

1. Yes it is ok to let the users enter the primary key. When you set it as the primary key it sets it as "no duplicates" so there isn't a chance that the user will enter a duplicate ID. Social Security number is a good example of a primary key that a user might have to type in.

2. As a matter of habit I think it is a good idea to have a primary key in all tables. I figure even if you aren't going to use it in a relationship, it might be nice to have it if you later do need it.

3. The primary key and it's cooresponding child (forign key) can be named the same but should definitly be of the same datatype. With the exception of the autonumber type field where the primary key would be autonumber and the child field needs to be number. (both long integer) However, if you are saying all 22 tables have the same name for the id fields, I don't think that is a good thing to do. Each primary key should be a different name. Not just "ID".

These are all just my opinions. Anyone feel free to correct me if I am wrong.

Dawn

http://www.calliopeconsulting.net

 

[LittleSmudge]

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 )

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190&CFID=77887448&CFTOKEN=87519146

{ 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

 

[chubby]

Dawnd3 and LittleSmudge I want to thank you both for your insight and knowledge, but QUESTION? I took a back-up copy of my database and took 15 minutes changing the primary keys in all 22 tables to names that uniguely ID each tables primary key.

example:
Victim TBL primary key was CASE # now it's VicCASE #.
Incident TBL primary key was CASE # now it's IncCASE #.
CRC TBL primary key was CASE # now it's CrcCASE #.
etc...

Then I when back and set enforce referential integrity cascade update/deleted related fields to all the tables and relationship types when from being one-to-many to all being one-to-one.

Why was that? And is this better or worst? Now I'm really lost. I'm wondering is this the correct way my tables should be setup. MMMMmmmmmmm!!!!

Help!!!!!!

 

[AlexMidd]

If you link a primary key in one table with a field in another table, it should always be one-to-many, otherwise there is no point in having a separate table. You need to work out which tables you actually need to be related and whether you want cascade updates/deletes - this is not always appropriate.

This highlights the need to sit down and design a database fully before actually creating it and "normalising" all the tables, i.e. making sure there are no one-to-one or many-to-many relationships. Have fun! )

Alex Middleton

 

[LittleSmudge]

In Access the # is used as the date value delimiter. What do you think you're meaning by

primary key was CASE # now it's CrcCASE #

etc. ?

G LS

 

[AlexMidd]

I presume they mean it for "Case number". A # symbol can be used in a field name but if it is used in a query it must be surrounded by suqare brackets, e.g. [CASE #]. However, I would discourage use of such symbols (as well as spaces) in a field name as it can cause problems later as well as confusion. Better names would be, e.g., VictimCaseNum, IncidentCaseNum etc. Have fun! )

Alex Middleton

 

[sko]

Hey guys,

Chubby has given us a good explanation of what he is trying to accomplish here [thread700-313201]

LittleSmudge,

This thread might ring a bell [thread700-300084] ;-)

Now maybe we're all on the same page.

 

[dawnd3]

Chubby, the reason it went from one to many to one to one is because you linked together a feild in each table that was set as a primary key. This make a big difference. This means that you can only have one record in the child table for each record in the primary or parent table. To fix it, go into the child table and make the foreign key NOT a primary key. For Example if I have a table of employee information and a table of department information and I wanted each department to belong to several employees (one to many) I would make the departmentID in the department table a primary key, and I would leave departmentID in the employee table as is, (Not a primary key) When I link these two fields up, I will have a one to many relationship. Does this help?

Dawn

http://www.calliopeconsulting.net

 

[chubby]

Well, I think I designed the database poorly from the start.
I'm trying to understand relational databases and fix mine as the same time. But everytime I read something, I feel I need to revisit what I've been doing.

And far as the Primary keys, I used CASE # because it was like that in excel. I took 12 really big excel files and imported them into access.

If I changed the primary key names to VictimCaseNum, IncidentCaseNum, etc is that really necessary? It's going to because problems, it tried it last night what a nightmare. All of my tables are back to one-to-many again...

 

[sko]

Chubby,

Let's take a small portion of your database as an example. One incident can have many victims.

tblIncident
CaseID -> Primary Key (Don't use the '#' in the name.)
Date
Sponsor
...


tblVictims
VictimID -> Primary Key
CaseID -> Foreign Key (or CaseRef as LS suggested)
LName
FName
...


The Incident table will hold all the incidents. Each incident will be identified by it's case number.

The Victims table will contain ALL the victims from ALL the cases. The victims table will have two keys. The primary key (VictimID) identifies the victim. The foreign key (CaseID) will tell you which incident this particular victim is associated with.

Your data might look like this

--------tblIncident---------

CaseID: 2468
Date: 7/1/02
Sponsor: Jones

CaseID: 1234
Date: 7/25/02
Sponsor: Jones


-------tblVictims-----------

VictimID: A123
CaseID: 2468
LName: Smith
FName: Bill

VictimID: B456
CaseID: 2468
LName: Smith
FName: Sally

VictimID: C279
CaseID: 1234
LName: Brown
FName: John


Now you have a one to many relationship between incidents and victims. If you have a form/subform the master/child field that links the forms would be CaseID. As you add the victims to the subform, the CaseID from the main form will automatically fill.

Is this any clearer?

 

[chubby]

1. So tblIncident has a primary key called CaseID.
2. The TblVictim has primary key called the victimID (which is auto generated) and a foreign key is the CaseID. Every other primary/foreign keys after that are auto generated and two per table... (is that right?)

ALLLLLLLLLLLLLLLLLLLLLLLL but the only problem is straighting out my primary/foreign keys out is to be a nightmare. And theres tons of data in 60% of the tables.
All the forms are completed, 90% of the queries are completed and 90% reports of the reports are developed, I'm screwed. I remember changing a primary keys last night screwed up everything. But I need to learn this and make my DB work so what next teach???

 

[chubby]

I give up!!! My database isn't petty, but it works...
I copied the back_end and tried to straight out the primary key problem, 'NOT'!!!! I caused even more problems. Auto numbering system wouldn't work right, I could link tables etc... Thanks for all the help but I think I'll build these last 5 reports stop and take a class or two just to get the basic down. This really is hard when you just don't know....

 

[AlexMidd]

Sure, you've got the short straw having to sort all this out. This often happens when a database starts off in a small way (or in Excel as in this case) and is not envisaged to become very large (I presume - otherwise it should have been planned properly from the start), but after a while it gets bigger and more complex. If it works and is not to be developed further, fine - just do what's easiest. If, however, it has to be developed further in the future (and this almost always happens even if it was never intended), then now is the time to get everything sorted out so that it can be easily altered in the future.

Just one thing, as I'm not sure it has been picked up. A primary key can be an autonumber but the foreign key should not. The two should be the same data type. An autonumber is a Long Integer so the foreign key should be set up as a Long Integer as well, but not as an autonumber, set it as Number then change the format to Long Integer.

Sorry if all this is getting too much but, as said elsewhere, this is not a trivial subject that can be learned overnight. Have fun! )

Alex Middleton

 

[chubby]

Well thanks to all... I'll just keep working on tried to improve my tables relationships first. That's the thing thats really getting me.

 

[Darrylles]

Hiya,

I think that this particular thread proves that 're-hash' isn't something to be applied to a medium-large sized database application.

I've demanded that I 're-write' the whole application in my company and 're-hash' has been the main response.
It results in mass confusion (at least for the developer who adopts the app.) and total false economy: it takes 10 times longer, and is still only 80% correct when it seemingly 'works'.

Regards,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."