DATABASES, TABLES AND QUERIES >> THE "RULE" FOR CREATING PRIMARY KEY FIELDS
Today, I answered a question regarding a change to a product code. Yesterday, I was called in to assist a VFP developer with a similar sort of issue. Yesterday it was a changing organizational unit ID. The problem in both cases was that the product code and organizational unit ID were both used as the primary key field and the value changed. In both of these cases, the problem could have been avoided had the developer simply applied "Ken's Hard and Fast Rule About Creating Primary Key Fields." There is only one rule for primary key fields:
"The primary key field has only one purpose - to uniquely identify a record within a table."
It therefore may not be used to identify a product or an organizational unit - it may identify a product record but not the product itself. It may be used to identify the orgainzational unit record, but not the organizational unit itself.
I know, it sounds like Ken has truely lost his mind - he is talking in circles. Take a look at the following:
The product code example was one that most can identify with so I will use it. In this case, the product code was something like 11-22-33-4444. For some reason management decided to change the product code. The new product code would be 11-22-33-4444-A and another related product would probably become 11-22-33-4444-B. This is probably a common enough scenario, but if you use the product code as the primary key, what happens? If you just change the product code, you will be left with all that historical data sitting there with no product record! Your inventory for product 11-22-33-4444-A will be reported as nil, but your warehouse manager will have tons of it in the warehouse. Your inventory table will have a large number of records for product 11-22-33-4444 but there will not be a product record to match and therefore, you will not have a product description.
So, how do you solve this problem? You begin by creating a new product ID. Now you go into every table that uses the product ID as the foreign key and you look for any instance of product 11-22-33-4444 and you change it to 11-22-33-4444-A. Go to the next table and do it all over again. When you have finally gone through every table, you now delete the old product record. As you can see, a daunting task to be sure. Oh wait - didn't they tell you? You have a list of 473 similar products that need their product ID's changed!
Lets apply "Ken's hard and fast rule about creating primary key fields." Management likes its product code. The designation 11-22-33-4444-A actually means something (or more likely, at one time in the far distant past, it did mean something.) Can you use the product code as the primary key? It is unique. It would therefore uniqely identify the record.
NOPE - don't you do it!
Instead, create another field and call it "ProductNo" or better still "RecordID" or even simply call it "ID." If you are working in VFP 8 or better, make this an autoinc field. and make this your primary key field. If you are working with VFP 7 or earlier, you will need to create a procedure that increments the record ID for you. (See my companion FAQ on creating your own primary key increment function.) The RecordID field has but one purpose - to uniquely identify the record. The productID can be used to identify the product, but the RecordID is yours and yours alone. Management does not get to change it - for that matter, management does not even have to know that it exists. You don't have to display it - nor do you have to print it.
Now let's see what would have happend had those two developers applied "Ken's hard and fast rule about creating primary key fields." Management says "I need you to change these 473 product codes." You say "OK - not a problem. Have the data entry people go ahead and change them - just make sure that the new product codes are unique or it will give them an error and they won't be able to change the ID for that product."
You still need to ensure that these new product codes are unique (make that index a candidate index,) but the user may change the product ID as needed. Because the product ID is not the primary key, it will never be used as a foreign key in another table (you use the RecordID instead.) If you only find the product ID in the product table, it only has to be changed in one place. If it only has to be changed in one place, you can let a data entry person do it. A far less daunting task - wouldn't you say?
@ 12/6/2006 8:17:50 PM
As we say in the States, you hit another home run. I only wish I had known this two years ago when I was designing my current database. I made the same mistake and am still paying for it. I did solve most of the problems by using update referential integrity but would not have had to do that if I had followed "Ken's Rule" for primary keys. If I ever get a chance to redesign it, I will definitely apply your rule.
Another rating for you.
@ 12/7/2006 5:56:10 AM
Thanks for the feedback. You can do a retrofit - it is a bit of work, but worth it in the end. That is what I was called in to help with on the Organizational Unit ID thing. We are going to apply "Ken's Rule" throughout the app. (It was their management that coined the phrase "Ken's hard and fast rule.")
@ 3/21/2007 3:02:17 PM
I really like your great idea this is a big help to me and to everybody who design database. I've learned many good advice from you, you're one of a kind!
God bless you Ken!
@ 3/21/2007 7:08:23 PM
Glad to be of help.
@ 10/2/2010 5:17:38 AM
[url=http://www.calvin-kleinunderwear.org]calvin klein shoes[/url]
Enter the code shown: