A rough example of an interface schema

This a question that new customers often face when doing integration work. This is a simple schema I offer as an example, feel free to use it as a starting point for considering your own schema design. This is a work in progress, it’s not by any means finished. If you are curious in seeing this go further let me know.

Globally Unique Identifiers for Primary Keys

Using globally unique identifiers can greatly simplify your data model. These are guaranteed to be unique across the entire database. The beauty of this approach is that you can now have say a Note table which can associate notes with anything, whether it’s a person, company, lab result etc. It allows a much smaller data model.

Person

I find it’s more effective to define staff, patients, next of kin just as a person and then you define relationships between people. It makes for a smaller data model. Personally I like just:

• Guid: ID for this person
• Sex: ‘M’, ‘F’, ‘U’, ‘O’
• Dob: date of birth timestamp
• Dod: date of death timestamp
• MaritalStatus: ‘M’, ‘S’, ‘U’

ExternalId

The ExternalId table defines all the external identifiers for any object. I like to use this to cover everything like:

• Id’s from other systems
• Can be staff id etc.
• SSN: Social Security Number
• Driver’s license
• Insurance plan IDs, i.e., say what ID number State Farm has for my health insurance.
• Group Insurance Number

The columns in this table are:

• ObjectGuid: primary key of the person, company or other thing the ID relates to.
• Id: the Id
• IdType: the type of ID, i.e., ‘SSN’, “DriverLicense’, ‘AcmeMrn’, ‘insurance_plan’, ‘group_insurance_number’

Address

The address of a person, company etc.

• Guid: of the address
• ObjectGuid: primary key of the associated person, company etc.
• Street
• Other
• City
• Zip
• Country
• AddressType: ‘home’, ‘business’, ‘birth’ (where they were born),’death’

Phone

• Guid: of the phone, allows notes to be associated with it
• ObjectGuid: what the phone number refers to, could be a company, person etc.
• CountryCode
• AreaCode
• Number
• Extension
• PhoneType: ‘home’, ‘business’, ‘contact’, ‘call_back’

Phone numbers can be associated with people, insurance companies etc.

PersonName

• Guid
• ObjectGuid: primary key of the associated person
• FirstName
• MiddleName
• LastName
• NameType: i.e., ‘alias’, ‘maiden’, ‘primary’

Health data often contains a lot of extra fields like Degree, Suffix etc. I think that it’s often redundant and better placed in a note associated with a person if the data actually matters.

Note

Notes can be related to any object, be it a person, company, lab result, sub lab test etc.

• Guid: the guid of the note
• ObjectGuid: what the note relates to: Notice it can be related to a person, company, lab result etc.
• Timestamp: when the note was created.
• Text: text of the note
• Author: Guid of the person that created the note, may be blank

Location

This is more intended for use in pinpointing a location within a hospital

• Guid
• Poc: point of care
• Room
• Bed
• Facility
• Building
• Floor

PersonLocation

• Guid: Guid of this record, useful for attaching a Note to this record.
• PersonGuid
• LocationGuid
• StartTimeStamp
• EndTimeStamp
• LocationType: ‘prior’, ‘temporary’, ‘current’, ‘pending’, ‘prior_temporary’

Relationship

This defines a relationship between two things. People and things can have multiple relationships.

• Guid: allows association of Note
• PrimaryGuid
• SecondGuid
• StartTimeStamp: can be blank for permanent relationships like kin
• EndTimeStamp: can be blank if no end.
• Relationship: ‘mother’, ‘father’, ‘referring_doc’, ‘attending_doc’, ‘consulting_doc’, ‘admitting_doc’, ‘guarantor’, ‘insurance_company’, ’employee’, ’employer’, ‘contact_person’, ‘observation_collector’, ‘copy_results_to’, ‘principle_result_interpreter’, ‘assistant_result_interpreter’, ‘technician’, ‘transcriptionist’, ‘diagnosing_doctor’, ‘patient’

Visit

• Guid: of the visit, this allows Notes and External keys to be associated with it.
• ObjectGuid: of the person associated with the visit
• StartTimeStamp: admission time
• EndTimeStamp: discharge time

Organization

These can be insurance companies, healthcare providers, employer.

• Guid: allows association with addresses, phone numbers people.
• Name: name of the company

InsurancePlan

This is a relatively sketchy area of the data model since it’s so ambiguous. I really have not fleshed it out.

• Guid: primary key of plan, allows notes and External Keys to be associated with it.
• StartTimeStamp: effective start date of the plan
• EndTimeStamp: expiration date of the plan
• InsuredGuid: the Guid of the Person who has the plan, i.e., parent who has the plan.

To associate the employer with a plan I would use the Relationship table. The Person, Relationship and Organization tables provide enough of a data-structure to get most of the information in to it.

Order

• Guid: allows notes, phone numbers to be associated with the order
• PatientGuid: the person for which the order is being made for
• PlacerGuid: the person or organization asking for the order
• FillerGuid: the person or organization that fulfils the order
• EnteredGuid: who entered the order
• VerifiedGuid: who verified the order
• EntererLocation: location of who entered the order
• OrderStatus: status of the order
• EffectiveTime

I left out the Placer Group Number and action by. The callback phone number can be associated with the order

ObservationRequest

• Guid
• PlacerGuid: the person or organization asking for the order
• FillerGuid: the person or organization that fulfils the order
• RequestDateTime
• ObservationDateTime
• ObservationEndDateTime

Observation collectors (people) can be related using the Relationship table. Order call back numbers can likewise be associated using the phone table. I didn’t create places for the results report status changing and so on. Transportation mode seems like something that belonged in a note. Haven’t covered the reason for the study. Did not cover the number of sample containers. Associated people like the principle result interpreter, assistant result interpreter, technician and transcriptionist seem best implemented using the Relationship table. I have not covered scheduling information here.

Diagnosis

By default we’ll assume the use of ICD9 codes for diagnosis codes.

• Guid: of the observation itself, allows association of comments.
• Code
• TimeStamp
• Confidential: true/false

I have not really covered the diagnosis related group, major category, outlier type, outlier days, outlier costs or the attestation date/time.

Observation

This is rather incomplete. Really the valid data here will vary greatly depending on the nature of observation, a.k.a lab result.

• Guid: of the observation, allows notes to be associated
• Value
• Units
• TimeStamp: time of the observation
• Abnormal: true/false
• LowRange
• HighRange

The responsible observer can be associated with the Relationship table. Likewise for the producer.

Extending the Paradigm

This approach to modelling data becomes very extensible. Let’s say we want to exchange EKG data. Then it become a more straightforward model to extend. If just think of an EKG being a big old blob of data. Now we can make a web service call that can return EKG data. We might need to have the ability to break it down into pieces with compression because it’s going to be quite a large amount of data to send over the wire. But we can keep that web service cleanly specialized on just that EKG data. If each EKG get’s it’s own GUID then we can use that to associate the EKG with other data. For instance:

• The associated patient would be an entry in the Relationship table, GUID of the Person representing the patient, with the relationship being ‘patient’
• The lab techs, organization that provided the EKG, the doctor ordering it etc. all can be specified by other entries in the Relationship table.

In this manner things become very flexible and make it much easier to cover the different ways that different heath institutions work.

Data Not Covered

I have not covered data like:

• Veteran’s military status
• VIP status
• Citizenship
• Ambulatory Status
• Financial data.
• Diet
• Employment status
• Much of the potential insurance information
• Household size