Another relationships question

[Pat]

I have a database that seems to have 2 lots of many to many relationships.

The database has a Case table as the primary table, hanging off it are a Family and Referral table.

I have assumed that a many to many exists between Case and Family and also between Case and Referral.

Cases can have more than one Family and a Family may have more then one Case.

Like wise Cases can have more than one Referrer and a Referrer may have more then one Case.

Would someone please check my relationships diagram and advise if i am on the right tram or not.

[JohnH]

Looks OK to me Pat, I think. You actually have 3 many to many relationships.
One case can have many screenings, and each DefScreenID can occur in many Screenings.

The fields in the Family table look like the fields for a Person table to me.
Without knowing the facts of the situation, I would have expected each case to involve 1 family, but the family to involve multiple people, each with a firstname and surname, and that potentially a person could belong to more than one family. It is hard to know where a family stops and another starts I suppose.

[Pat]

Yes you are right, there are 3 many to many relationships.

[HansV]

In addition to John's remarks (with which I agree): what is the purpose of the CaseIDxxx fields in tblReferer and tblFamily? Are they remnants of an older setup? If so, I'd remove them.

[Pat]

Any fields i don't wish to use anymore because of irrelevance i add xxx at the end of the name, down the track i always delete them after doing a Find and Replace search.

[HansV]

OK, fine.