confusing relationships

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/For...esign/200603/1

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@uwe...
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

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
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

Thanks Allen for your lightning fast reply...

Yeah, now its making more sense to me....i wuold rather have it the way you
have said..but how does that solve the issue of a contact having multiple
spouses....and multiple children??

You have to excuse me if its a dumb question....i'm no software designer...


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

Hi All,

[quoted text clipped - 42 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Thanks Vincents for the reply...

Vincent Johns wrote:

Even if some of the people in your database will never be policyholders,

No this database is being designed for a company that deals with other
peoples insurance policies...so all their clients have some sort of an
insurance policy...

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.

OK, this is on the lines of Allen Brownes reply...

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.

How do you do this???
Do you mean store all the information abt the insurance in one table ...say
[Insurance], have that linked to the [Persons] table with foreign keys ??
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.

You are right, i'm at my wits end here!!

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?
It is a sequence of policies covering different time periods...to cover that..
..i have another table [PolicyType] which has all policy types (time periods
etc) which is linked to the insurance table with 1-many...

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.


Now before you people replied, i did some changes to the db, and this is how
it looks now...http://members.westnet.com.au/mukund/rel.gif

-- Vincent Johns
Please feel free to quote anything I say here.

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

[quoted text clipped - 56 lines]

kingnothing

--
Message posted via http://www.accessmonster.com

The answer to your question will depend what you need to store.

Using the example from:
http://allenbrowne.com/AppHuman.html
you could create a group of type "marriage", with 2 people in
tblGroupClient. If you want to track the full history of a person's
marrigage, these groups will need to be limited by date, i.e. the marriage
started on m/d/y and ended on m/d/y (blank if still current.) You will also
need BirthDate and DeathDate fields in tblClient, and based on all those
dates you could then retrieve the name of the person's current spouse(s).

--
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:5d9f85ab18158@uwe...
Thanks Allen for your lightning fast reply...

Yeah, now its making more sense to me....i wuold rather have it the way
you
have said..but how does that solve the issue of a contact having multiple
spouses....and multiple children??

You have to excuse me if its a dumb question....i'm no software
designer...


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

It is an excellent way to do it....but i think that is probably an overkill
and besides i would not know how to manipulate the Date() (and date related
fucntions) function very easily to achieve the result.
I'm looking at a very simple db.

Thanks,

kingnothing

Allen Browne wrote:
The answer to your question will depend what you need to store.

Using the example from:
http://allenbrowne.com/AppHuman.html
you could create a group of type "marriage", with 2 people in
tblGroupClient. If you want to track the full history of a person's
marrigage, these groups will need to be limited by date, i.e. the marriage
started on m/d/y and ended on m/d/y (blank if still current.) You will also
need BirthDate and DeathDate fields in tblClient, and based on all those
dates you could then retrieve the name of the person's current spouse(s).

Thanks Allen for your lightning fast reply...

[quoted text clipped - 26 lines]
at:
http://allenbrowne.com/AppHuman.html

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

(My comments are in-line.)

kingnothing via AccessMonster.com wrote:

Thanks Vincents for the reply...

Vincent Johns wrote:

Even if some of the people in your database will never be policyholders,


No this database is being designed for a company that deals with other
peoples insurance policies...so all their clients have some sort of an
insurance policy...

Does that include children, too?

[...]
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.


How do you do this???
Do you mean store all the information abt the insurance in one table ...say
[Insurance], have that linked to the [Persons] table with foreign keys ??

No, you'd store insurance-policy information in the [Insurance] Table,
and personal information about policy holders, spouses, children,
beneficiaries, &c., in a [Persons] Table, along with whatever other
information you'd need there.

The foreign keys that I was talking about weren't all that "foreign", as
some of them would point back to the same Table. For example, the key
in some record in [Persons] identifying a spouse would have the same
value as the primary key of some other record in the [Persons] Table,
and that other record would describe the spouse of the person described
in the first record. But both records would be in the [Persons] Table.

A foreign key in [Persons] pointing to a record in [Insurance] 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".

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.

You are right, i'm at my wits end here!!

Following Allen's suggestions may seem to involve extra work right now,
but I think you'll be happier with the results once the database is set up.

[...]
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?

It is a sequence of policies covering different time periods...to cover that..
.i have another table [PolicyType] which has all policy types (time periods
etc) which is linked to the insurance table with 1-many...

So I guess you're saying that each policy in [Insurance] has a link to
[PolicyType] which identifies the type of policy. That sounds good. At
the least, I'd expect [PolicyType] to include the name of the policy.

[...]

Now before you people replied, i did some changes to the db, and this is how
it looks now...http://members.westnet.com.au/mukund/rel.gif

Of course, some of that Relationships window I can't see, but I have a
couple of suggestions about what I can see.

I notice that [Contact] and [Spouse] both contain fields that look like
[First Name]. (This was what Allen and I suggested should be combined
into one Table.) Even more obviously, I could see no differences at all
in the structures of [Contact-Insurance-Policy] and
[Child-Insurance-Policy] and [Spouse-Insurance-Policy] -- they even had
the same primary key names! 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. I'm not suggesting
that Access cares what you call these things, but it's important for you
to keep in mind what everything means, and naming the objects well will
help greatly. (Access may not care about the names, but not combining
those similar Tables will impact your design, and I suggest you try to
do something about that.)

I notice that the names of your primary keys end with "ID", a good way
to distinguish them from data fields. However, I've found it helpful to
go a step further -- I usually name my primary keys to match the name of
the Table, followed by "_ID". So, for example, I'd rename [rowID] to
[Child-Insurance-Policy_ID], both in its own Table and wherever it's
used as a foreign key.

You apparently didn't specify that referential integrity be enforced on
your relationships. It's not necessary to do that, at least at first,
but doing so will help protect you from having keys that don't refer to
any actual records, and once your Tables are defined, I suggest you
change the links as well to enforce referential integrity on them.

Here's a semantic question: Is a person listed in [Contact] a
policyholder, or a sales prospect, or possibly someone not even
associated with any insurance policy? I notice an asymmetry between
[Contact] and [Spouse] records. How would you record that both spouses
in one family are policyholders? For [Contact], there appears to be no
key linking to any [Spouse], whereas for [Spouse] there is a link to
[Contact].

-- Vincent Johns
Please feel free to quote anything I say here.

Sorry Vincent I still cant work it out...

I have one table called person and another table called insurance. Now Person
is related to Insurance by a 1-many relationship with Person_ID(PK) and
Insurance_Peron_ID(FK). That works fine. Regarding the inner join (Table
calling itself) for the spouse i just cant figure it out...

I have called a field in Person as Spouse ID with number as the data type.
Dragged the Person table again...and linked Person_ID to spouse ID in a 1-
many rel (without ref integrity). When i try to enter data into the contact
table, i get an error saying" you cannot add or change record because a
related field is required in table person" . If i can get this thing working ,
i will forever be greatful to you guys...

Regards,

kingnothing
Vincent Johns wrote:
(My comments are in-line.)

Thanks Vincents for the reply...

[quoted text clipped - 3 lines]
peoples insurance policies...so all their clients have some sort of an
insurance policy...

Does that include children, too?

[...]
You could include some other fields, such
as foreign keys back to the same [Persons] Table to identify an insured
[quoted text clipped - 3 lines]
Do you mean store all the information abt the insurance in one table ...say
[Insurance], have that linked to the [Persons] table with foreign keys ??

No, you'd store insurance-policy information in the [Insurance] Table,
and personal information about policy holders, spouses, children,
beneficiaries, &c., in a [Persons] Table, along with whatever other
information you'd need there.

The foreign keys that I was talking about weren't all that "foreign", as
some of them would point back to the same Table. For example, the key
in some record in [Persons] identifying a spouse would have the same
value as the primary key of some other record in the [Persons] Table,
and that other record would describe the spouse of the person described
in the first record. But both records would be in the [Persons] Table.

A foreign key in [Persons] pointing to a record in [Insurance] 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".

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.

You are right, i'm at my wits end here!!

Following Allen's suggestions may seem to involve extra work right now,
but I think you'll be happier with the results once the database is set up.

[...]
Is exactly one insured person named on each policy? Does your 1-to-many
[Contact] to [Insurance] relationship correspond to multiple
[quoted text clipped - 4 lines]
.i have another table [PolicyType] which has all policy types (time periods
etc) which is linked to the insurance table with 1-many...

So I guess you're saying that each policy in [Insurance] has a link to
[PolicyType] which identifies the type of policy. That sounds good. At
the least, I'd expect [PolicyType] to include the name of the policy.

[...]

Now before you people replied, i did some changes to the db, and this is how
it looks now...http://members.westnet.com.au/mukund/rel.gif

Of course, some of that Relationships window I can't see, but I have a
couple of suggestions about what I can see.

I notice that [Contact] and [Spouse] both contain fields that look like
[First Name]. (This was what Allen and I suggested should be combined
into one Table.) Even more obviously, I could see no differences at all
in the structures of [Contact-Insurance-Policy] and
[Child-Insurance-Policy] and [Spouse-Insurance-Policy] -- they even had
the same primary key names! 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. I'm not suggesting
that Access cares what you call these things, but it's important for you
to keep in mind what everything means, and naming the objects well will
help greatly. (Access may not care about the names, but not combining
those similar Tables will impact your design, and I suggest you try to
do something about that.)

I notice that the names of your primary keys end with "ID", a good way
to distinguish them from data fields. However, I've found it helpful to
go a step further -- I usually name my primary keys to match the name of
the Table, followed by "_ID". So, for example, I'd rename [rowID] to
[Child-Insurance-Policy_ID], both in its own Table and wherever it's
used as a foreign key.

You apparently didn't specify that referential integrity be enforced on
your relationships. It's not necessary to do that, at least at first,
but doing so will help protect you from having keys that don't refer to
any actual records, and once your Tables are defined, I suggest you
change the links as well to enforce referential integrity on them.

Here's a semantic question: Is a person listed in [Contact] a
policyholder, or a sales prospect, or possibly someone not even
associated with any insurance policy? I notice an asymmetry between
[Contact] and [Spouse] records. How would you record that both spouses
in one family are policyholders? For [Contact], there appears to be no
key linking to any [Spouse], whereas for [Spouse] there is a link to
[Contact].

-- Vincent Johns
Please feel free to quote anything I say here.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

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@uwe...
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/For...esign/200603/1