Primary Key & Normalization of Data (1 Viewer)

[pgilmour24]

Hi, complete novice here again, but I have a couple of questions about primary keys and the normalization of data.

I work at a lighting design company and I want to increase productivity by creating lighting, circuit and switchplate schedules, product sheets, orders, invoices etc. but last time I did any Access dbase work was about 10 years ago. Anyways to help me with this project I bought the Access Bible 2016 which has been great but has also brought up a few questions that I can't find specific enough answers on paper to.

To explain my question in relation to my work: we have several projects, each with their own projectID (currently generated by hand) which constitutes of the last two digits of the year and then a sequential number - eg 20 1345. I plan on using code to generate these projectIDs when a new project is added. Should I use this generated projectID as my primary key, or just have an autonumber as the primary key and then the generated projectID as a separate field? What are the pros and cons to both? I have read a couple of different fourm posts with contrasting info! Some say the primary key should be meaningful and others say meaningless...?!


To help explain the next question I have put my plan for the start of the tables below:

tblProject:
ProjectID (generated code which will always be unique/autonumber - see above) primary key
ProjectAddress
Client
ContactNum
etc

tblCircuitSchedule
ProjectID - foreign key
ProjectCircuitNumber - (autonumber which returns to 1 when working on a different project) indexed
Fitting Code indexed
Room
etc

Data from tblCircuitSchedule will look something like this

20 1345 / 1 / W1 / Sitting Room
20 1345 / 2 / D1 / Sitting Room
20 1345 / 3 / W2 / Kitchen
20 1789 / 1 / W1 / Hallway
20 1789 / 2 / W2 / WC

Can I have a combination of fields to act as my primary key - I know something like this is called a composite key but is it the best way to do this or should I have a table per project where the ProjectCircuitNumber is the primary key? Or am I completely barking up the wrong tree?
Thanks in advance

 

[theDBguy]

Hi. First of all, if you want sequential numbers that reset to 1, you can't use an Autonumber field. Autonumber fields are not guaranteed to be sequential, only unique. They can even be negative numbers.

Yes, you can use a composite primary key. I would probably use a date instead of just the last digits of the year.

 

[Isaac]

Should I use this generated projectID as my primary key, or just have an autonumber as the primary key and then the generated projectID as a separate field?

Use an autonumber as the primary key. Users do not see or have any awareness of this.

Create additional column(s) as needed that the business might want to see and use - quote ID's, whatever.

#10 (access-programmers.co.uk)

 

[plog]

I have read a couple of different fourm posts with contrasting info! Some say the primary key should be meaningful and others say meaningless...?!

If you having meaningful, unique and immutable data that can act as a primary key you should use it. 95% of the time this does not exist, so in those cases you should use an autonumber primary key as the primary key. For example, in the US a table of states could use the US Postal code abbreviation for a state as its primary key. A table of people however would use an autonumber primary key, because you cannot count on personal data to satisfy those characteristics (more than 1 John Smith in the world, Bruce Jenner is no longer Bruce Jenner).

Should I use this generated projectID as my primary key, or just have an autonumber as the primary key and then the generated projectID as a separate field?

You should use an autonumber primary key. Further, I don't think you should use this generated project ID any more. What exactly does it do for you? Does having it really accomplish anything within your organization? Often not. Just use the primary key as the projectID and save yourself a ton of work.

 

[Isaac]

If you having meaningful, unique and immutable data that can act as a primary key you should use it.

And, to me at least, the point of the discussion is that your concept of "immutable" should be challenged to the point where you conclude "I guess I really don't have any way of knowing that it will never change - or that it will always be unique".

My stance is ... Any key that is made of out of pure data, you can't know it will always be unique. Period. You can risk thinking it probably will always be unique, but that's a risk you take.. And the price of being wrong can be catastrophic to recover from, and probably involve the most stressful month of Sundays you'll work remediating it in your life. : |

 

[The_Doc_Man]

I tend to be a pragmatist. You can use an autonumber for lots of display purposes as long as there is NO ASSUMPTION of contiguous ordered numbering or other inherent meaning. E.g. like plog said, use the autonumber of the project table as the project number. Just never use DMax function on that number to mean anything. I sometimes allow the internal autonumber as the "confirmation ID" since it is an independent number that won't be changed and which uniquely points to a transaction. And it doesn't reveal anything about other internal numbers.

You certainly CAN use a compounded index as a primary key, but just remember that if the table with that key has child data, you have to duplicate the compound key for every related child. OR you can autonumber that table and use the autonumber for the child, but place a compound index on the fields so that you can use them efficiently for searching by name/number/whatever.

When it comes to actual keys for internal relationships, smaller is better. When it comes to searches, meaningful is better.

 

[pgilmour24]

If you having meaningful, unique and immutable data that can act as a primary key you should use it. 95% of the time this does not exist, so in those cases you should use an autonumber primary key as the primary key. For example, in the US a table of states could use the US Postal code abbreviation for a state as its primary key. A table of people however would use an autonumber primary key, because you cannot count on personal data to satisfy those characteristics (more than 1 John Smith in the world, Bruce Jenner is no longer Bruce Jenner).



You should use an autonumber primary key. Further, I don't think you should use this generated project ID any more. What exactly does it do for you? Does having it really accomplish anything within your organization? Often not. Just use the primary key as the projectID and save yourself a ton of work.

Thanks for this. The projectID will always be unique. If a project is cancelled etc then we don't re-use that number, just incase it starts up again or someone needs the drawing for future use. It is used to reference CAD drawing packages and designs as well as order references so removing it is not an option.

Following yours and everyone else's advice, I think I will use the autonumber within the database and have a separate field where I will just input the actual projectID that we use office side.

 

[pgilmour24]

And, to me at least, the point of the discussion is that your concept of "immutable" should be challenged to the point where you conclude "I guess I really don't have any way of knowing that it will never change - or that it will always be unique".

My stance is ... Any key that is made of out of pure data, you can't know it will always be unique. Period. You can risk thinking it probably will always be unique, but that's a risk you take.. And the price of being wrong can be catastrophic to recover from, and probably involve the most stressful month of Sundays you'll work remediating it in your life. : |

I tend to be a pragmatist. You can use an autonumber for lots of display purposes as long as there is NO ASSUMPTION of contiguous ordered numbering or other inherent meaning. E.g. like plog said, use the autonumber of the project table as the project number. Just never use DMax function on that number to mean anything. I sometimes allow the internal autonumber as the "confirmation ID" since it is an independent number that won't be changed and which uniquely points to a transaction. And it doesn't reveal anything about other internal numbers.

You certainly CAN use a compounded index as a primary key, but just remember that if the table with that key has child data, you have to duplicate the compound key for every related child. OR you can autonumber that table and use the autonumber for the child, but place a compound index on the fields so that you can use them efficiently for searching by name/number/whatever.

When it comes to actual keys for internal relationships, smaller is better. When it comes to searches, meaningful is better.

Wait! I have changed my mind - I am going to use the ProjectID as the primary key. I am 100% certain that it will not/cannot be used again and it is meaningful data. I just need to write some code to automatically generate this number now... Thanks for all of your help, I will probably be back again shortly to ask more questions.

 

[tmyers]

Sounds like you are on the same path I am. I am also designing a DB for Lighting. I am however on the distributor side of things, so my design/needs are probably different.

 

[Gasman]

Wait! I have changed my mind - I am going to use the ProjectID as the primary key. I am 100% certain that it will not/cannot be used again and it is meaningful data. I just need to write some code to automatically generate this number now... Thanks for all of your help, I will probably be back again shortly to ask more questions.

Don't do it.
Use an Autonumber and just have your ProjectID for visual purposes.

Just my 2p worth thrown in.

 

[tmyers]

I am probably the last person to offer advice, but I 100% agree with Gasman. If there is even the slightest doubt the your ProjectID will not be 100% unique, use an autonumber.

 

[The_Doc_Man]

My more pragmatic viewpoint says if the project ID has structure then internally use an autonumber and have the project ID as a separate (or compounded) field. If it always and only presents as a simple-looking number that otherwise has no meaning AND is not subject to an auditor getting bothersome about non-continuous numbers, then using the project ID as a PK is not terrible. The last time I wanted to use an actual app-related number as a PK, it turned out that the security auditors demanded certain properties of the ID that were not possible with autonumbers. So even though everything else was right, that externally applied "standard" bit me in the butt and I had to "roll my own" numbers for public consumption and revert to an autonumber internally.

 

[Isaac]

Wait! I have changed my mind - I am going to use the ProjectID as the primary key. I am 100% certain that it will not/cannot be used again and it is meaningful data. I just need to write some code to automatically generate this number now... Thanks for all of your help, I will probably be back again shortly to ask more questions.

Out of curiousity.

If you someday have a project. The project starts off, picks up some key information, (possibly even incurs a tiny bit of cost, forecast, approval from another dept, etc). Then it gets cancelled or shelved for 6 months. Within the same year, "the" project gets re-started and breathed life back into.
Your natural reaction is to either update the information on the existing project ID, or, create a new project ID.
But, one of your managers or leadership or business partners have informed you that they'd like the original data that the first draft project picked up, to stay with it, for the sake of recordkeeping. They also want to see the same project ID on screen (now), with new data attached to it.

What will you do?

 

[pgilmour24]

Out of curiousity.

If you someday have a project. The project starts off, picks up some key information, (possibly even incurs a tiny bit of cost, forecast, approval from another dept, etc). Then it gets cancelled or shelved for 6 months. Within the same year, "the" project gets re-started and breathed life back into.
Your natural reaction is to either update the information on the existing project ID, or, create a new project ID.
But, one of your managers or leadership or business partners have informed you that they'd like the original data that the first draft project picked up, to stay with it, for the sake of recordkeeping. They also want to see the same project ID on screen (now), with new data attached to it.

What will you do?

This is a good question - assuming the project itself has not changed, we would just add a revision to any documentation that we have produced if anything else had changed. As the house name/location/address/client is the same there is no reason for the existing projectID information to change. If the design itself changes, then we just add a revision to the documentation and disregard any previous versions. Of course if the client upped and sold the house we had initially done the design on and bought another one, then that would warrant a new projectID. Does that make sense?