Tricky Relationships

[CoddFish]

I am trying to decide how I should set up the relationships between the following entities:

- SPECIALIST
- CASE LOAD
- AREA.

A SPECIALIST is an employee that is responsible for a CASELOAD. Each CASELOAD can belong to only one SPECIALIST at a time, but CASELOADS can be shifted from one SPECIALIST to another (that is the confusing part, I think).

Each CASE LOAD consists of a group of AREAs. An AREA is a combination of three things: ZipCode, FacilityType, and County.
Each combination of these three fields is unique to an AREA.

I think I'm tripping up in deciding whether or not a CASELOAD needs to exist as an entity that is separate from a SPECIALIST since each CASELOAD can belong to only one SPECIALIST (at a time) and each SPECIALIST can have one and only one CASELOAD. My conceptual problem comes from the fact that although this appears to be a one-to-one relationship, how do I account for the fact that a CASELOAD can shift from one SPECIALIST to another SPECIALIST (i.e., in the event of the termination or the re-assignment of a SPECIALIST). Additionally, even if a SPECIALIST is terminated, he or she does not get deleted from the database, but is simply flagged as inactive (i.e., the Active? field in tblSPECIALIST is unchecked so that the value becomes "No").

Also, please keep in mind, I want it to be easy for an administrator to quickly reassign all AREAs in a CASELOAD to a different SPECIALIST when necessary.

Please help me clarify the appropriate relationships between these three entities.

Thanks!

-CoddFish

 

[Pat Hartman]

I want it to be easy for an administrator to quickly reassign all AREAs in a CASELOAD to a different SPECIALIST when necessary

- AREA is not directly related to specialist. Area is related to caseload and caseload is related to specialist. Therefore, specialist exists in the caseload table as a foreign key to the specialist table. Specialist does NOT exist as a field in the Area table. That leaves only a single row to be changed if a caseload is assigned to a different specialist.

 

[CoddFish]

Pat,
Specifically, what are the relationships?

 

[Pat Hartman]

tblSpecialist:
SpecialistID (autonumber primary key)
etc.

tblCaseLoad:
CaseLoadID (autonumber primary key)
SpecialistID (foreign key to tblSpecialist)
etc.

tblArea:
AreaID (autonumber primary key)
CaseLoadID (foreign key to tblCaseLoad)
etc.

 

[CoddFish]

So, you are then suggesting the following:

SPECIALIST - one-to-many - CASELOAD
CASELOAD - one-to-many - AREA

Am I correct in this assumption?

 

[Pat Hartman]

Yes, but if you want specialist to be 1-1 with caseload, just put a unique index on the specialistID in the caseload table.