Re: confusing relationships

Dear Kingnothing:

I've been reading what you and other have posted. What I'm concerned about
is knowing what the entities are referenced by the table you propose to
have.

Let's concentrate for a moment on the Insurance table. Is this a table
where each row is one policy? Or is each row an insurance plan, to which
many insured persons may be associated? For some reason I am suspecting the
latter. In this case, the relationship between "insured persons" (contact
table?) and Insurance has to be many-to-many. Many persons may purchase any
given plan, and any person may purchase more than just one plan. Is that
the case?

"contact can have multiple insurances, children and spouses"

I don't know if I like the many-to-many relationship between contact and
spouse. I'm not sure whether you're modeling polygamy here, or divorces, or
widowed persons who have remaried. Is that your intent?

Before modeling the contact/spouse/children aspect, I suggest you get very
familiar with just what functions family relationships need to have in the
finished product. A person could be a spouse and a child (of his or her
parents). Just how far does this need to go?

Consider that a husband and wife are both spouses. They may both be
insured. In this sense, they are equivalent. Now if John and Mary are wed,
and each has a policy, then do you really want both to be contacts as well
as both being spouses. Think about this. If they move, you'd have 4 places
where you update their address and phone. That's not so functional!

This thing does not appear simple to me. I have handled such requirements,
but it is significantly complex. It's going to be fairly difficult to hash
through all of it in a newsgroup.

Hope this gives some insight.

Tom Ellison


"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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200603/1


.

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
    ... 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: Inconsistent Democrats?
    ... >>> a child or not have a child. ... Yet these very same democrats are also ... >>> saying that when it comes to getting health insurance, ... > child have health insurance coverage. ...
    (soc.retirement)
  • Re: Inconsistent Democrats?
    ... >>> a child or not have a child. ... Yet these very same democrats are ... >>> saying that when it comes to getting health insurance, ... > child have health insurance coverage. ...
    (soc.retirement)
Loading