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

 

[CoddFish]

I did this because I thought different (valuable) people might look in one forum, but not the other. Additionally, I wasn't sure whether this topic better fit into the "General" forum or the "Tables" forum -- so I decided to put it into both. Is there some operational, organizational, or political reason why this is bad practice? Please let me know...

Thanks.

-CoddFish

 

[CoddFish]

[No message]

 

[CoddFish]

Is it not for me to decide what the "correct answer" is? Additionally, I believe that the spirit of a discussion forum is to support the free flow of knowledge. I know you (and others) don't get paid -- but was that ever the expectation or the founding purpose of this forum.

I apologize if I have wasted your time or offended you in any way Pat. But, I'm not quite naive enough to think that I should buy into your limited view of what you purport not to be political.

A sincere thank you to all who have and will reply to this and future threads.

 

[Rich]

You need to get off your high horse. Your definition of forums is not supported here nor any of the Access forums that I've visited over the years, in fact on the vast majority of other forums your duplicate posts would simply be locked by the moderators. This isn't a winner takes all debate, nor is it "political" to ask members not to post under multiple topics, it's just common courtesy.

 

[Mile-O]

A quick look at the historical posts here would show that most users take the time to review all forums.

 

[CoddFish]

Noted.

 

[CoddFish]

And, a somewhat ironic title (subject) for the thread, no?