Re: confusing relationships

There is no unique right way to organize your information, but some structures will make your work much easier than others. I agree with Allen Browne, and I have a couple of suggestions that I hope may be of use.

Even if some of the people in your database will never be policyholders, my guess is that there are many fields common to [Contacts], [Spouse], and [Child], such as name, sex, and birth date, and maybe health information. If so, I suggest putting all of those common fields into one combined [Persons] Table. You could include some other fields, such as foreign keys back to the same [Persons] 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 (it depends on what you want to do with the information), but I can see tricky situations, such as to which of several spouses a particular child should be linked. Or maybe you want a many-to-many children to parents relationship defined. (You would probably need another Table in which each record identifies a link between some child and some parent, similar to the [tblClient] linking Table in Allen's sample database.) Do some of these named spouses also have multiple spouses whom you need to track? If so, this might call for another many-to-many relationship, and another linking Table to manage it. (You might be able to combine two such linking Tables, if they contain similar information.)

Is exactly one insured person named on each policy? Does your 1-to-many [Contact] to [Insurance] relationship correspond to multiple beneficiaries, or would it be a sequence of policies covering different time periods?

You may find that it will make sense, at first, to make some simplifying assumptions, and try to set up a working database based on those, and then embellish it later. For example, if you have only two clients with multiple marriages, you might include a [Notes] field (memo data type) in which you record special circumstances, and then manually update the special information applying to those two clients, until you have standard procedures in place to deal with them.

-- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
Please feel free to quote anything I say here.


Allen Browne wrote:
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


kingnothing via AccessMonster.com wrote:

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

  • 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: confusing relationships
    ... Not sure I would use separate tables for contact, spouse, and child. ... PolicyHolderID foreign key field in the Insurance table, ...
    (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)
Loading