Need help with relationships

[itsuit]

I'm now at the "can't see the forest for the trees" point with trying to figure out how to form relationships with these tables, and it's making my head hurt. Can anyone help me figure out how to join the following:

SQL Server 2000 backend, Access 2000 front end:

I have the following tables:

tblClaimants
tblClaims (details of the claimant's injury, case number, etc., since each claimant can have more than one claim)
tblProviders (doctors)
tblCenters (the offices doctors work from)
tblSchedule (doctor's schedule for a given day)
tblAppointments (claimant's appointments with doctors)
tblAppointmentTypes (subject of the appointment)
tblAttorneys (claimant's attorney)

Each claimant can have many appointments with different doctors. When the appointment is entered, it must fall within the doctor's schedule for that day (e.g., on March 5th the Dr. is working from 8:00am to 3:00pm, so the appointment has to be within those hours).

Each claimant will only have one attorney, but an attorney can represent many different claimants (so I can figure out the one-to-many join on those).

Not sure if I need a separate AppointmentDetails table with this, or if that info should just be stored within tblAppointments.

I appreciate any direction you folks can give me. Meanwhile, I'm going to take some Tylenol...

--------
Kyle

 

[GingerR]

Hi Kyle. Looks like you've done a good job so far! Not sure what else I can add to it....

Do you have to have a relationship between a claimant and a provider, or can a claimant just have an appt with a provider without a prior relationship? What I mean is, do you have to necessarily store the relationship between a doctor and a claimant, or can the relationship just exist in a Claim? Seems like the latter is what you have going, which is fine I'd think.

Does tblAttorneys just have a unique record for each Attorney? I would think so. Since you say a Claimant can have only one attorney...I wonder if instead, a CLAIM can have one attorney, so put a field for AttorneyID into tblClaims? Why I'm asking is this: If I have an injury today, and my attorney is Mr. Smith, then I have an injury next year, can't I have a different Attorney? Like my question about doctors above, I don't think it's necessary to retain a relationship between a claimant and an attorney...the relationship only exists in a Claim, right?

Another thing: not sure if I'm right here or not, but I wonder if Attorneys can be added to tblProviders? With an additional field of "Group" (Doctor, Lawyer), you could put all doctors and lawyers in the same table. I don't know if that makes sense or not, but if you are gathering the same info about them (Phone, Address, Name, etc) then why not?

Do the appointments reference a ClaimID?

Do you also have to track Attorney Appointments?

Without seeing your entire table structure, I'm not sure what else you are looking for here. Your tables seem to make sense (with possible changes per my questions above), so I assume each table is set up properly such as

ClaimantID
ClaimantName
ClaimantAddress

ClaimID
ClaimantID
DoctorID
LawyerID
Injury


etc.

Do you need more help? If so, post your table structures and what you need help with.

Thanks!




Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[itsuit]

Ginger -

Thanks so much for your detailed response! I appreciate the insight.

tblProvider must have a relationship with tblAppointments, because they need to make sure they don't overbook a Dr. on the same day at the same time.

I didn't even think of putting the attorneys in tblProviders, just because I wanted to enforce the Center Code from tblCenters for all doctors, but that's not true for attorneys; however, I suppose I could just do that with validation.

Your suggestion about linking tblAttorneys to tblClaim (instead of tblClaimant) was great - you're right about that.

Thanks again for giving me some direction (Validation? Reason for being?) here. I'll post back if any of this escapes me again.

---------
Kyle

 

[ZmrAbdulla]

Also refer to Geomedics Downloads may give you some more information in this. There are some cool free databases in this subject
Regards

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[itsuit]

Zameer -

Thanks for that link - you're right, some interesting stuff there. However, the people I'm working with are not medical providers, they just set up independent medical examinations (IMEs) for insurance companies.

Thanks again for the info.

---------
Kyle

 

[willir]

Kyle

Good work in your deisgn -- looks like an appointment / claims database.

You may be at the stage to test your design with sample data.

Place some raw data into your tables, and then create sample queries to test and ensure you can retrieve your information.

I find working with test data is a very helpful stage which is may be skipped because it can be a bit tedious -- but it is a lot easier to correct a design issue than to correct data issue due to a design issue after your system is in production.

The one gotcha that you may experience is defining something as a 1:M relationship when in fact you have a M:M relationship.

Other things to consider...
- Do you need to preserve history? For example, on claimant has one lawyer, but what if the lawyer needs to transfer clients? Or if the claimant comes back again? For example, do you mean a Case will have one Claimant and one Lawyer, or do you mean a Claimant will have one Lawyer?
- Security
- Backups

And perhaps Leslie / LesPaul can inpart some wisdom since she works in a legal / court environment.

Richard

 

[itsuit]

Richard -

Thanks for the input. Funny you should mention it, because I spent a good part of yesterday working with sample data and testing my connections to the SQL Server (so far, so good).

There's no need to preserve history for this application - "today" is the only time period this client cares about. However, your comment about case-lawyer versus claimant-lawyer was good, so I'm going to adjust for that (claimants can, in fact, change attorneys mid-stream).

Now I have to move on to the other Access forums to get help on methods for capturing when a report has been printed and recording that to a table. I think the fun is just beginning with this one.

Thanks to all for some good advice.

------------
Kyle