Re: confusing relationships

Not sure I would use separate tables for contact, spouse, and child.

It seems to me that these are people who could have policies in their own
right, so it makes more sense to me to put all the people in a single table
with a ClientID primary key. Your Insurance table would then contain foreign
keys for:
PolicyHolderID relates to Client.ClientID
Spouse relates to another record in Client table.
...

If one person has multiple policies, it might make sense to just have the
PolicyHolderID foreign key field in the Insurance table, and have all the
other family information in a different table. For a downloadable example of
how that might work, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"kingnothing via AccessMonster.com" <u18754@uwe> wrote in message
news:5d9ec64a4bb43@xxxxxx
Hi All,

Firstly thank you for helping me with the initial hurdle of how to set up
a
DSN etc. Now i have done that successfully and am in the process of
designing
a database. I seem to have been stuck in a place i cant get out of.
The situation is like this..

Tables
---------
A contact table - Usual stuff here
An Insurance Table - All Insurance details
Spouse Table - Info about spouse
Child table - Child Info

Requirements
------
contact can have multiple insurances, children and spouses..
Each Child can have multiple insurances
Each spouse can have multiple insurances

Contact is the primary table....with insurance, spouse and child tables
linked in 1 - many relationships

Relationships
-------
Contact linked to Insurance with Contact ID (Primary key) and Foreign key
in
Insurance 1-many
Contact linked to Spouse with Contact ID (Primary key) and Foreign key in
Spouse 1-many
Contact linked to child with Contact ID (Primary key) and Foreign key in
Child 1-many

Simillarly Spouse and Child are linked to Insurance with Spouse ID , Child
ID
and Foreign key with 1-many

The problem.....I dont know if this is the right way of doing it??

Can someone please advice me on this...

Regards,

kingnothing


.

Relevant Pages

  • Re: confusing relationships
    ... You could include some other fields, such as foreign keys back to the same Table to identify an insured person's spouse, or parent, etc. Trying to maintain this personal information in several separate Tables is likely to create headaches for you, as you'll have to do the same work several times in designing and using those Tables. ... How you should identify multiple spouses I'm not sure, but I can see tricky situations, such as to which of several spouses a particular child should be linked. ... Does your 1-to-many to [Insurance] relationship correspond to multiple beneficiaries, or would it be a sequence of policies covering different time periods? ... If one person has multiple policies, it might make sense to just have the PolicyHolderID foreign key field in the Insurance table, and have all the other family information in a different table. ...
    (microsoft.public.access.tablesdbdesign)
  • confusing relationships
    ... An Insurance Table - All Insurance details ... Spouse Table - Info about spouse ... Child table - Child Info ... Contact linked to Insurance with Contact ID and Foreign key in ...
    (microsoft.public.access.tablesdbdesign)
  • Re: confusing relationships
    ... Let's concentrate for a moment on the Insurance table. ... A person could be a spouse and a child (of his or her ... Child table - Child Info ... Contact linked to Insurance with Contact ID and Foreign key ...
    (microsoft.public.access.tablesdbdesign)
  • Re: HELP! I cant keep track family address, phones, dates, etc.
    ... >linking to an individual child page of our info, ... >our spouse, children and so on for a couple of generations. ... PersonID ... Instead the date that a marriage relationship ...
    (microsoft.public.access.tablesdbdesign)
  • Re: confusing relationships
    ... insurance policy... ... For example, the key in some record in identifying a spouse would have the same value as the primary key of some other record in the Table, and that other record would describe the spouse of the person described in the first record. ... A foreign key in pointing to a record in would identify an insurance policy, not a person, so it probably doesn't seem as strange to call that kind of key a "foreign key". ... I would have lots of trouble remembering which Table each refers to, never mind that the Queries based on them are likely to be extra complex to design and test. ...
    (microsoft.public.access.tablesdbdesign)