Data Design question

Question

I am having a tough time with this design problem and would appreciate any insight.

i have a doctors office that is provided certain privileges currently there are only 5 privileges but more could be added. Each of these privileges has a status of Yes or No, but there could be a finer grained status in the future and each of these privileges are related to a location(er,inpatient, outpatient) and they too could expand in future.

so currently i have the tables OfficePrivileges, PrivilegeLocation, PrivilegeType, PrivilegeStatus.

OfficePrivilege is a Joining table between PrivilegeLocation and DoctorOffice. It has a double primary key of OfficeID and PrivilegeLocationID.

At one time I had Type and status joined to the OfficePrivileges Table then switched to have the type table be a child of Location and status be a child of type. They are all single primary key tables.

if you were designing this set of tables how would you do it? I am thinking that this is almost a hierarchy problem..and I hate them. I would like to lay out the edit screen as crosstab table having Location across the top, Type down the side and details being the statuses. That is how it is currently in the system I am trying to integrate to, but its a cobol backend and handles hierachies better than relational db....

EDIT:To Help clear up confusion:

for my example there are Admit Privileges, Attending Privileges,Consulting privileges and Surgery Privilges. And the locations are Inpatient, ER, OutPatient, and OP/Surgery. Currently the statuses are only Yes or NO. But they could change in the future depending on client need.

This information is stored in tables in my database.

Answer 1

I think the big thing here it to try to be as flexible as possible, since you're aware of "possible" changes, but you don't really want to code to those, yet.

Something like what you've got is really probably pretty good: I'd go this way: you'll actually have a 3-way relationship between Offices, PrivilegeLocations, and PrivilegeTypes

I would say your OfficePrivaleges table should have the following 6 columns: Id (its own PK), OfficeId, LocationId, PTypeId, StartDate, EndDate When a new Privilege is granted for an Office, you'll add a record to this table linking the three and adding the first date the office has the privilege in StartDate. If the Privilege is revoked, add that date to EndDate. If it's renabled, either add a new row, or reset the StartDate.

I would avoid making PrivilegeType a child of PrivilegeLocation, because then you either have to store every location n times (n = different privilege combinations). This way, you're only storing Offices, Locations, and Types once each.

So, for instance, Doctor A could have Inpatient Privileges at Location X, but only DX Imaging privileges at Location Y, while Doctor B could have Inpatient privileges at both Locations X and Y.