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.
Another relationships question
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Another relationships question
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 287
- Joined: 09 Mar 2010, 23:16
- Location: Canberra Australia
Re: Another relationships question
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.
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.
Regards
John
John
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Another relationships question
Yes you are right, there are 3 many to many relationships.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Another relationships question
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Another relationships question
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.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands