Modeling a Health-System Account Hierarchy When Clinicians Work at Several Sites

Design a data model that can represent an account hierarchy in healthcare. There are several reasonable ways to model it, and each has different consequences. Choose one, and explain why you prefer it to the alternatives. You are also asked to present the design as a short slide deck. Use this branch of a hierarchy as the reference example: - Hospital system: Kaiser - Hospital: Kaiser South San Francisco - Department: Cardiology - Several doctors (for example, Doctor A and Doctor B) - Nurses - A receptionist The structure is not a clean tree. A healthcare provider can belong to more than one location: a doctor might work at the South San Francisco hospital on some days and at a San Mateo location on others. Some providers also run a private practice on the side, so the same person can appear at different levels, and branches of the hierarchy can have different depths. ```hint Store the hardest record first Take the doctor who splits the week between two sites and also has a private practice, and try to record that person in each candidate model. Notice which models force you to copy the doctor or lose information. ``` ### Clarifying Questions - Who uses the hierarchy and for what: field reps planning visits, managers rolling up activity by health system, data stewards maintaining it? Which queries must be fast? - Should individual people be accounts in the same structure as organizations, or a separate entity linked to them? - Do we need history, such as where a provider worked last year or which system a hospital belonged to before a merger? - How deep can the hierarchy get, and how often does it change compared with how often it is read? ### What a Strong Answer Covers - Requirements drawn from the example: variable depth, people at several locations, side practices, and the queries the model must answer - At least three candidate models compared against those requirements, with clear trade-offs - One chosen model with its entities, keys, relationship attributes and integrity rules - How common queries (roll-ups, a provider's locations, everyone under a system) run, and how moves, departures and mergers are handled - A clear, slide-ready explanation of the choice ### Follow-up Questions - Two hospital systems merge. What changes in your data, and what happens to last quarter's roll-up reports? - A dashboard counts unique doctors per hospital system. How do you avoid counting a doctor twice when they work at two hospitals in the same system? - How would sales territories and record visibility be layered on top of this hierarchy?