8 Replies Latest reply on Jul 9, 2010 1:16 PM by philmodjunk

    Why Record ID's should not be used as Primary Keys in Filemaker Relationships.

    philmodjunk

      Title

      Why Record ID's should not be used as Primary Keys in Filemaker Relationships.

      Post

      Every now and then, I encounter a database created by another that uses a calculation field or auto-entered data field with Get ( RecordID ) as the calculation expression, and it is then used to uniquely identify each record in the table and also to link to records in related tables.

      At first glance, it looks like a very reasonable alternative to using an auto-entered serial number field as the Primary Key, but there's a subtle limitation to this approach that will result in a major headache. Sooner or later, you will need to import this data into a different filemaker database either to recover the data from a corrupted file or simply to deploy a newer version with updated table definitions. If you rely on Get ( RecordID ) as your primary key, this import likely will visit chaos on your data and relationships. This will happen because each imported record is automatically assigned a new record ID which may be completely different from its original value.

      Consider this simple example with a table with 3 records:

      Record ID      Data      
      1                      Alpha
      2                      Beta
      4                      Upsilon

      If I save a clone of this file and import this data into my clone, I will get:

      Record ID      Data      
      1                      Alpha
      2                      Beta
                           Upsilon

      And the third, Upsilon record will no longer link to the same related records as they all have 4 in their match fields.

      Simply sorting the source file's found set in descending order by "Data" just before importing would produce these Record ID's:

      Record ID      Data     
      1                      Upsilon
      2                      Beta
                           Alpha

      Furthermore, there is no way to alter a record's Record ID in filemaker so you have no way to correct this error after the fact. You'd have to sort your original file by Record ID and carefully insert new dummy records to fill in each "gap" in the Record ID sequence, import the data and then delete the dummy records in the new file after the import is complete.

      Thus in Filemaker databases, a simple auto-entered number field remains the simplest, safest way to implement a Primary Key.

        • 2. Re: Why Record ID's should not be used as Primary Keys in Filemaker Relationships.
          eibcga

          I have found that the record ID's purpose is to assign a dynamic number that is dependent on a given record's relative position or 'rank' in a list of records (be that a list of records that are unsorted, sorted, a found set, or all records).

           

          It seems to me that a primary key should uniquely identify the record itself, regardless of the record's relative position in a list of records.  In other words, using a static number key identifier, rather than one that is dynamic.

           

          So I agree — Using a record ID as a primary key does not serve this purpose and therefore, should not be used as the key field.

           

           

           

           

          • 3. Re: Why Record ID's should not be used as Primary Keys in Filemaker Relationships.
            comment_1

             


            eibcga wrote:

            I have found that the record ID's purpose is to assign a dynamic number that is dependent on a given record's relative position


            I am afraid you are confusing RecordID and RecordNumber.

             


            • 5. Re: Why Record ID's should not be used as Primary Keys in Filemaker Relationships.
              etripoli

              I switched from using 'arbitrary' auto-enter key fields to meaningful single and multi-field keys whenever possible, and it makes databases much easier to manage.  And by meaningful, I mean things like Customer ID, Product ID, Order Number, Delivery Number, Employee ID, etc.  Information that is actually useful for the end user.  The Get ( recordid ) field is still there, especially because it's makes restoring data from backup easier when a user accidentally deletes a record.

               

              So, while I agree that Record ID's should be used as the primary key, they should still be a part of every table - AND, the primary key shouldn't always be an auto-enter serial number.  Especially when you allow users to import data into tables 'on their own', and then they forget or don't know to check the 'Auto-Enter' box during the process.

              • 6. Re: Why Record ID's should not be used as Primary Keys in Filemaker Relationships.
                philmodjunk

                Any time you use data that isn't "meaningless" and is automatically supplied by filemaker as your primary key, you are asking for trouble.

                Consider the headaches if you enter such data incorrectly and then don't discover the error until after several days of data entry have generated records in related tables linked by this key. Correcting the error without losing any of the related records can be a real chore and risks loss of data if you make a mistake.

                Also the very fact that the Primary Key now has "meaning" to the user invites them to require you to make changes in the content and format of this data resulting in major headaches for you.

                Consider this true story:

                I once worked for a company that had an Access DB that was updated daily with SKUs (Stock Keeping Units) generated from a "big iron" database. These SKUs had the format: C1234546 where C was a letter that was supposed to be C for production material and T for Test material (new material being evaluated for use in production.) My predecessor designed the database to use this SKU as the primary key in the Access database. From time to time, our supply chain manager determined that such T materials had been approved for actual production use, opened up "Big Iron" and updated the SKU from T to C. I then would discover  that the Bill of Materials for an item on our production schedule did not list all the materials needed to manufacture it. Had I failed to check every such item on the schedule carefully, a significant production delay or even a production error could have ensued. Simply using a meaningless, hidden, auto-generated Primary Key would have avoided the entire hassle...

                Using keys with meaning are very useful fields for finding, sorting, filtering records, but using them for the Primary Key that links your tables is not a good idea.

                PS. Given that almost every table I create has an auto-entered serial number field, I've never needed a field defined with Get ( RecordID ). My auto-entered serial number field can do everything it can and without the limitations I documented at the start of this thread.

                • 7. Re: Why Record ID's should not be used as Primary Keys in Filemaker Relationships.

                  Whatever choice you make and what ever system you employ, a serious crash can wreck havoc and lead to many problems.

                  For instance, in the 'old' days a 110 file solution had one of the major files become corrupted and I had to replace it from a backup.

                  Here is where it gets interesting. The replacement file will create 'next in line' id numbers but these will match up with the wrong related records from the existing file.

                  The simplest solution is to advance the auto-serial number far enough ahead so that it does not match up with existing related records. You can check each related file for the highest number and act accordingly.

                  Now you can rebuild the missing records between the old number and the new number, lets say 1120-2000 and if done correctly the newly added rebuild records will match up with all related records.

                  The alternative is to replace all of the files from the backup and re-enter all of the data with this full replacement avoiding the mismatching of the records.

                  PS: using two or more values in one id field, such as C101...105 and T234...400 is a BAD idea. Better to use one serialized id field with multiple fields in the record to be used for that other information. Thus the links remain true no matter how this other id field is altered.

                  • 8. Re: Why Record ID's should not be used as Primary Keys in Filemaker Relationships.
                    philmodjunk

                    "using two or more values in one id field, such as C101...105 and T234...400 is a BAD idea."
                    Agreed, that's not how I would have designed it. That was the established design in "Big Iron" that I had to deal with. Separating the values in the Access database was an upgrade on our to do list at the time I left the company.

                    Managing serial number settings on import is fairly easy to do in a script.

                    You can determine the next serial value either by sorting the records by serial number and going to the first or last record, you can define a summary field that computes the Maximum of the field or with the proper relationship defined you can use the Max () function.

                    Once you've determined the max serial value in existance, your script can use set next serial value to set the next serial value to max value + 1.