1 Reply Latest reply on May 7, 2014 3:54 PM by philmodjunk

    Question on unique IDs

    terrypink

      Title

      Question on unique IDs

      Post

           Hi everyone,

            

           If there's anyone out there that's a database design expert, I would really appreciate a response?

            

           I've been tasked with creating a database that consists of three tables (but two of the tables are being pulled from other databases). 

            

           One table is called: Patient (that’s the one stored internally), the other two that are being pulled from outside sources are called Food and Diary.

            

           I have a primary key in the Patient table, but the vendors providing the data on the Food and Diary tables want two separate unique IDs to link to their databases.  Is that even possible if the relationship flows in the direction of Patient to Food/Diary (with Patient being on the “one” side of a “one-to-many” relationship?  I would think that there can only be one primary key on the Patient table, and this would have to be the same key that would then link to the foreign keys in both the Food and Diary table.  But the vendor is saying I would just create two separate unique IDs from the Patient table and link them to their Food and Diary table (they are saying they need the two unique IDs for IRB reasons).

            

           Any guidance would be truly appreciated,

           ~Terry

            

        • 1. Re: Question on unique IDs
          philmodjunk

               There is no setting in FileMaker that designates a field as the official "primary key". We simply use auto-entered serial number fields and/or text fields with auto-entered Get (UUID) values with unique value validation set to produce a unique ID field and start using it as the primary key. There is no reason that you can't set up and use more than one field as a primary key on the same table, it's just unnecessarily redundant from a design point of view.

               So if they want two different match fields in the relationship, for two fields that both uniquely identify the patient, there's no reason why you can't do that in order to meet their requirements.