Foreign Keys for Databases

• Pass foreign keys in as arguments
• Add foreign keys to child tables after they are populated
• Generate GUIDs and use these to associate records together

Introduction

This is a common conceptual problem that people need to solve. Most databases involve relationships between data: for instance, a one-to-many relationship of lab results to a patient.

So when mapping, for example, HL7 data to a database, you need to have a solution to take a primary key, such as a patient ID, and associate it with related data.

Three typical solutions discussed are:

• Pass foreign keys, such as Patient ID, as arguments into mapping routines which need them
• Add foreign keys to child tables after they are populated
• Generate GUIDs and use these to associate records together

See this profile for one example of handling this problem.

Pass foreign keys in as arguments [top]

This is one technique of handling foreign key relationships in data. Just pass in the foreign key into the routine used to map the data.

Add foreign keys to child tables after they are populated [top]

This is an alternative way of putting in a foreign key. It’s slightly more modular, since the AddPatientId() function can be put into a shared helper library.

Generate GUIDs and use these to associate records together [top]

This is another technique for adding foreign keys. In this case, we generate a random GUID and add it in:

util.guid() is a method supplied by iNTERFACEWARE. It generates a cryptographically strong unique global identifier.