Notifications
Clear all

How to deal with optional business keys within a link that will eventually show up


Posts: 85
Topic starter
(@geekn)
Trusted Member
Joined: 7 months ago

Let's take a scheduling system where appointment slots are created by the business.  That appointment "may" eventually be related to a provider to distinguish an open appointment slot for a provider.  That provider slot "may" eventually be related to a patient.  The standard workflow for an appointment is:

  • An open time slot/appointment is created for the next 30 days (patient/provider not required)
  • Slot/appointment "may" be assigned to a provider eventually
  • Slot/appointment "may" be assigned to a patient eventually
  • Same slot/appointment may be reassigned to a different patient (original patient cancelled)
  • Same slot/appointment may be reassigned to a different provider (patient shows up and provider is sick)

As I think about this from the DV book perspective, I'm inclined to represent this as a single LINK (L_APPOINTMENT) where HK_Appointment is the driving key since both the Patient and Provider may or may not come along later.  Assuming I have zero key records represented as -1 for the sake of this discussion, this is how I see many appointments playing out in terms of link data.

DAY 1

HK_Appointment     HK_Provider     HK_Patient

1                           -1                     -1

DAY 2

HK_Appointment     HK_Provider     HK_Patient

1                           1                     -1

DAY 3

HK_Appointment     HK_Provider     HK_Patient

1                           1                     1

In the book, this condition is presented in the context of a flight number change which would tend to happen infrequently.  In the case of appointment, this is actually the primary behavior and would cause many LINK records to be created for the same logical appointment slot. The source data is sitting in a single table in the source system so my initial inclination was to put these in a single link table, but should they be broken out into separate links tables based on unit-of-work?

  • L_APPOINTMENT_PATIENT
  • L_APPOINTMENT_PROVIDER
  • L_APPOINTMENT_PROVIDER_PATIENT

Having separate links means that each link has it's own satellite with the same fields as every other LINK/SAT since they would be pulled from the same source table which seems strange.

Or should this simply be modeled as a single appointment hub where the business key is the timeslot/scheduled provider.  Then, as patients or providers are assigned to it, they end up being represented in a multi-active satellite hanging off the appointment hub.  The three separate relationship entities could then be represented in the business vault by applying business rules (ex: L_APPOINTMENT_PROVIDER_PATIENT = query Appointment SAT where I have a PatientID and ProviderID)

Reply
17 Replies