-2

People say that the primary key for a table should ideally be an integer "Id" which has no meaning in the outside world. Under these circumstances how does one handle the following situation in a clinical database?

Patient is a table containing one record per patient with a primary key Id. Admission is a table containing one record per admission to hospital containing a field PtId linked to the Id field in the Patient table.

Clearly when someone records a new admission the Patient.Id field is not available. What mechanism should one use to obtain the Patient.Id value prior to inserting the new record into the Admission table?

Of course, there is a real world field HospitalNumber which should be unique for each patient.

Does one always have two fields in this situation the primary key Id with no outside world meaning and a second unique key HospitalNumber with meaning to the outside world?

Michael Oddie
  • 104
  • 1
  • 10
Tony Wolff
  • 99
  • 2

2 Answers2

2

First - if the Patient table has an internal unique ID (often, but not always, an integer value), and a distinct real-world ID, then there are a few rules that should normally be followed:

  • The internal ID is normally going to be the primary key; however, the "real_world" ID should ideally be constraint to be unique.
  • The application should allow the user to enter the real-world ID in order to fin the patient in the system.
  • Ideally, the internal ID should not be displayed to the user - because they don't have any real need for it.

Also, of course, if the patient is not in the system at all, the application should allow a new patient to be added, and (if necessary) be able to create or retrieve a new valid HospitalNumber.

So, applications should be designed so that the correct foreign record can be located by the values in said record that are meaningful outside the system, and then (based on the record selected by the user) should get the internal ID of the record, and store it as the desired foreign key.

Side note: For me, the best reason to have an ID value that is not meaningful to users is that anything that's meaningful to users can change.

As an example: in a system I used to work with, the unique ID for an employee was used as their username in the system. This value was usually formed by combining the employee's location ID with their initials.

What this lead to was a desire to change this ID when a user changed their name (got married or divorced, for instance), or changed their location. The process of changing an employee's ID touched over a dozen tables, and potentially tens of thousands of rows; so, updating the employee's ID could take 10-15 minutes, and would frequently cause blocking of many other users. On average, there was a change that actually impacted the users at least once a month.

If the vendor had one field for the employee's userid, and a separate field (that no user ever needed to see) for the employee's ID, then these delays wouldn't have happened.

RDFozz
  • 11,631
  • 4
  • 24
  • 38
0

In my system I have an "Account" table which has both "account_id" (PK, UID) and "account_number" (UK, UID) the account number is what the app uses rather than the account id and it gives you more freedom in assigning that number. It basically means there's no constraints on you assigning whatever number you like to whatever patient you like (as long as it's still unique). A primary key / Unique id should be assigned sequentially using a sequence. I also think there's probably some security concern about using your PK outside the database but I'm no expert on that.

Michael Oddie
  • 104
  • 1
  • 10