From Dwayne Wright - www.dwaynewright.com
As you know, the there are many diverse discussions on what is a primary key in a relationship and what is not. I’ve recently seen a posting where someone was creating a database to manage buildings for a client. Instead of relying on a auto generated primary key data string, they were using the building number. So any client record that was attached to a building was linked by the building number being in common between the two records.
For example 123 Elm Street would be the primary key field data in the Buildings table and an associated client would be linked with that same text string in the foreign key field in the clients table.
However, what if you happen to have two building records in the database from two different cities with 123 Elm Street? What is the name of the street changes (it does happen)? What if someone did a typo on the street address but it wasn’t updated for days, weeks or months later?
That is the problem with using primary key data based upon information within the record. If the data changes, all relational database links to that record (using it’s primary key) would also have to be updated.
At some point in time, the user would most definitely need to switch over to an auto enter primary key field that doesn’t have an “information” relationship to the record. It is simply an address. My mailing address doesn’t tell you that much about me as a person but it does reliably get my mail to me. In my opinion, data within primary key fields should never have any "Associative Intelligence" behind their design or implementation.
That doesn’t mean that the serial number or sku number you show your user cannot have intelligence within them. I do this all the time at the client request. They may refer to a record id number that has aspects like department number, timestamp, status and the like. For all they know, that is the primary key field for the database. However, under the hood, I have my own key id field for use as a primary key.
As a side note, with the advanced version of FileMaker 8 and above, it is very easy to copy and paste fields into new tables. This makes it very easy to have a consistent method of adding a serialize primary key in every table you create. Simply copy it from an existing one and paste it into any new ones. In fact, I often do that when working with databases provided by a client. I might not update all their relationships to the new key but it’s there for any new design I add. I also tend to comment the living daylights out of what I’m doing in cases like this. That is a nod to future self when I have to work on the solution again, as well as any other developer that may end up working on the solution.
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 2007 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.