confusing relationships

[kingnothing via AccessMonster.com]

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

 

[Allen Browne]

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

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[kingnothing via AccessMonster.com]

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

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

 

[kingnothing via AccessMonster.com]

Thanks Vincents for the reply...

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

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

[quoted text clipped - 56 lines]

 

[Allen Browne]

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

 

[kingnothing via AccessMonster.com]

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

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]

 

[Vincent Johns]

(My comments are in-line.)

Thanks Vincents for the reply...




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

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 <[email protected]>
Please feel free to quote anything I say here.

 

[kingnothing via AccessMonster.com]

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

(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".

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Tom Ellison]

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]

Hi Tom,

Thanks for the reply...It has got me thinking as to what needs to be done.
Now, I'll let you know the situation in detail...

The contact is the person who has signed to have his insurance taken care of
by the company that needs this database.

This person(primary contact) can have one (lets assume one for simplicity)
wife/husband(spouse) whose insurance policy is also handled by the company.
Simillarly he can have multiple children each with insurances handled by the
company.

I have a contact table, which has all the details like, name , dob, address,
etc. The primary key is an autonumber Contact_ID

The insurance is a combination of different things that make up a policy,
like insurance number date purchased, date of renewal, premium, insurance
company , comission that this company gets to manage the insurance etc.

I understand that the insurance can be many types like life insurance,
income protection etc.To achieve this, I have another table called [Policy],
which has all the different types of policies.

There is another table called [Insurance Policy]
{id}
{Insurance ID}
{Policy Type} --> Combo
box with row source query =Select Policy Type from [Policy]
This table is linked to the Insurance Table insurance table with many - 1
(respectively) ==> For every insurance record, the [Insurance Policy] table
will have multiple entries.

Now all i need to do is to be able to achieve this with all details of
contacts, his/her spouse and their insurance details ...

Hope that gives you a better idea..

Thanks

kingnothing

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

Hi All,

[quoted text clipped - 42 lines]

kingnothing

 

[Guest]

Just out of curiousity, why does anyone care if related people all have
insurance? Shouldn't a policy just be concerned with the person the policy
is taken out on? Why are you trying to track all of these combinations of
people?

Hi Tom,

Thanks for the reply...It has got me thinking as to what needs to be done.
Now, I'll let you know the situation in detail...

The contact is the person who has signed to have his insurance taken care of
by the company that needs this database.

This person(primary contact) can have one (lets assume one for simplicity)
wife/husband(spouse) whose insurance policy is also handled by the company.
Simillarly he can have multiple children each with insurances handled by the
company.

I have a contact table, which has all the details like, name , dob, address,
etc. The primary key is an autonumber Contact_ID

The insurance is a combination of different things that make up a policy,
like insurance number date purchased, date of renewal, premium, insurance
company , comission that this company gets to manage the insurance etc.

I understand that the insurance can be many types like life insurance,
income protection etc.To achieve this, I have another table called [Policy],
which has all the different types of policies.

There is another table called [Insurance Policy]
{id}
{Insurance ID}
{Policy Type} --> Combo
box with row source query =Select Policy Type from [Policy]
This table is linked to the Insurance Table insurance table with many - 1
(respectively) ==> For every insurance record, the [Insurance Policy] table
will have multiple entries.

Now all i need to do is to be able to achieve this with all details of
contacts, his/her spouse and their insurance details ...

Hope that gives you a better idea..

Thanks

kingnothing

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

Hi All,

[quoted text clipped - 42 lines]

kingnothing

 

[Tom Ellison]

Dear Kingnothing:

I was toying with the idea of having just a single table of persons.
Self-referencing links could determine relationships (spouse, father,
mother). An attribute of this table could be Contact, a yes/no. Filtering
on this would give you only those currently in your contact table.

I would keep a separate table of addresses. Husband, wife, son, and
daughter could link to the same address record (including phone and other
details if desired) so that, when someone moves, everyone in a family group
would "move" together with only a single change.

This is a bare outline of just a portion of the system.

A complete job would be very considerable. But proper table designs are the
foundation of all the work that follows. If you get a bad table design, the
work you build on top of that can be a huge waste.

Normalization of tables isn't just some dry set of rules, not to me. It is
fundamental to the proper way of thinking about a database. The rules are
there to guide you to being able to properly analyze, categorize, and
synthesize (and any other "ize") the solution. The rules are natural and
essential ways of thinking.

Tom Ellison

Hi Tom,

Thanks for the reply...It has got me thinking as to what needs to be done.
Now, I'll let you know the situation in detail...

The contact is the person who has signed to have his insurance taken care
of
by the company that needs this database.

This person(primary contact) can have one (lets assume one for simplicity)
wife/husband(spouse) whose insurance policy is also handled by the
company.
Simillarly he can have multiple children each with insurances handled by
the
company.

I have a contact table, which has all the details like, name , dob,
address,
etc. The primary key is an autonumber Contact_ID

The insurance is a combination of different things that make up a policy,
like insurance number date purchased, date of renewal, premium, insurance
company , comission that this company gets to manage the insurance etc.

I understand that the insurance can be many types like life insurance,
income protection etc.To achieve this, I have another table called
[Policy],
which has all the different types of policies.

There is another table called [Insurance Policy]
{id}
{Insurance ID}
{Policy Type} -->
Combo
box with row source query =Select Policy Type from [Policy]
This table is linked to the Insurance Table insurance table with many - 1
(respectively) ==> For every insurance record, the [Insurance Policy]
table
will have multiple entries.

Now all i need to do is to be able to achieve this with all details of
contacts, his/her spouse and their insurance details ...

Hope that gives you a better idea..

Thanks

kingnothing

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

Hi All,

[quoted text clipped - 42 lines]

kingnothing

 

[kingnothing via AccessMonster.com]

Just out of curiousity, why does anyone care if related people all have

insurance? Shouldn't a policy just be concerned with the person the policy
is taken out on? Why are you trying to track all of these combinations of
people?

Thats one of the requirements that i have been given. Its because, if a
client (the main contat) calls this company , they should be able to retrive
all the information regarding his/her spouse and children.

Hi Tom,

[quoted text clipped - 82 lines]

 

[kingnothing via AccessMonster.com]

Please see inline..

Dear Kingnothing:

I was toying with the idea of having just a single table of persons.
Self-referencing links could determine relationships (spouse, father,
mother). An attribute of this table could be Contact, a yes/no. Filtering
on this would give you only those currently in your contact table.


I would keep a separate table of addresses. Husband, wife, son, and
daughter could link to the same address record (including phone and other
details if desired) so that, when someone moves, everyone in a family group
would "move" together with only a single change.

This is what i have been told by all the kind souls that are trying to help
me. But what eevryone fails to understand is that i dont know how to do it.
If you can be kind enough and explain the same in a detailed fashin, it would
be very helpful. If you can, please represent it a little graphically for me
to understand.

I was also wondering if i should just leave the relationships out of the
table and just create joins in Queries and make those queries the source for
my forms. Is that how it works??

This is a bare outline of just a portion of the system.

A complete job would be very considerable. But proper table designs are the
foundation of all the work that follows. If you get a bad table design, the
work you build on top of that can be a huge waste.

I realise that and am striving to achieve that perfect database design .

Normalization of tables isn't just some dry set of rules, not to me. It is
fundamental to the proper way of thinking about a database. The rules are
there to guide you to being able to properly analyze, categorize, and
synthesize (and any other "ize") the solution. The rules are natural and
essential ways of thinking.

Tom Ellison

Hi Tom,

[quoted text clipped - 97 lines]

 

[Tom Ellison]

Dear Kingnothing:

Putting the relationships into the table design is an essential. There is
something called referential integrity.

If you have a one-to-many relationship you must not allow the rows on the
many side to be "orphaned". That means, you cannot delete a row in the
table on the "one" side when there are rows on the "many" side. You must
either delete them, too, or prohibit deleting the "one". That's the job of
referential integrity. Try it! You'll see what I mean.

So then, without enforcing referential integrity, there is little likelihood
the database will work well later when you write those queries.

Tom Ellison

Please see inline..

Dear Kingnothing:

I was toying with the idea of having just a single table of persons.
Self-referencing links could determine relationships (spouse, father,
mother). An attribute of this table could be Contact, a yes/no.
Filtering
on this would give you only those currently in your contact table.


I would keep a separate table of addresses. Husband, wife, son, and
daughter could link to the same address record (including phone and other
details if desired) so that, when someone moves, everyone in a family
group
would "move" together with only a single change.

This is what i have been told by all the kind souls that are trying to
help
me. But what eevryone fails to understand is that i dont know how to do
it.
If you can be kind enough and explain the same in a detailed fashin, it
would
be very helpful. If you can, please represent it a little graphically for
me
to understand.

I was also wondering if i should just leave the relationships out of the
table and just create joins in Queries and make those queries the source
for
my forms. Is that how it works??

This is a bare outline of just a portion of the system.

A complete job would be very considerable. But proper table designs are
the
foundation of all the work that follows. If you get a bad table design,
the
work you build on top of that can be a huge waste.

I realise that and am striving to achieve that perfect database design .

Normalization of tables isn't just some dry set of rules, not to me. It
is
fundamental to the proper way of thinking about a database. The rules are
there to guide you to being able to properly analyze, categorize, and
synthesize (and any other "ize") the solution. The rules are natural and
essential ways of thinking.

Tom Ellison

Hi Tom,

[quoted text clipped - 97 lines]

kingnothing

 

[kingnothing via AccessMonster.com]

Yeah i know about referential intergrity. so, you are saying that
relationships are absolutely imperative for the database design, right!!
Please look at http://members.westnet.com.au/mukund/rel.gif and tell if if
there is anything wrong with the way the database is designed (Apart form the
fact that i will be storing simillar records in different tables)

PS: if you want a clearer picture let me know, i can make another one

Regards,

kingnothing

Dear Kingnothing:

Putting the relationships into the table design is an essential. There is
something called referential integrity.

If you have a one-to-many relationship you must not allow the rows on the
many side to be "orphaned". That means, you cannot delete a row in the
table on the "one" side when there are rows on the "many" side. You must
either delete them, too, or prohibit deleting the "one". That's the job of
referential integrity. Try it! You'll see what I mean.

So then, without enforcing referential integrity, there is little likelihood
the database will work well later when you write those queries.

Tom Ellison

Please see inline..

[quoted text clipped - 51 lines]

 

[jacksonmacd]

This is what i have been told by all the kind souls that are trying to help
me. But what eevryone fails to understand is that i dont know how to do it.
If you can be kind enough and explain the same in a detailed fashin, it would
be very helpful. If you can, please represent it a little graphically for me
to understand.

In order to grasp the idea of a self-join, you must have a firm
understanding of the conventional one-to-many relationship like you
might have between (e.g.) Orders and OrderDetails. I presume you know
how to open the Relationship window, add the two tables (Orders,
OrderDetails), and drag the primary key onto the foreign key, and to
enforce referential integrity.

Now for the self join. Imagine your Contacts table has an autonumber
primary key of ContactID and another field (long integer) called
ParentID. Set the Required property for ParentID to No. Your task is
to define the relationship between parents and their children.

Open the Relationships window and add the Contacts table. Now add a
second instance of the Contacts table. It will be aliased to
Contacts_1. Drag the Contacts.ContactID onto Contacts_1.ChildID.
Enforce referential integrity on the relationship.

Starting at the top, you have a record for "Peter" (contactID=5), who
obviously has a parent, but you don't know who it is. You delete his
ParentID. Now work with "Fred" who is Peter's son. Enter 5 into Fred's
ParentID. Repeat this process for all of Peter's children.

Of course, you will create forms to manage all this stuff.

Depending on your data requirements, you could have a SpouceID in your
contacts table. This design would limit your database to a single
spouce per contact (!!!). Alternatively, you could create a Marriage
table if it were important to track multiple marriages.

HTH

**********************[email protected]
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Vincent Johns]

Hello, kingnothing, I'm back...

At the risk of reiterating my previous comments, I don't agree with your
"apart from...", since maintaining those separate but largely isomorphic
Tables implies that you'll later be essentially REQUIRED to do a lot of
work destroying them (=moving their contents to a well-designed Table),
after you've done work building them, when you could do that right now,
while you're still designing the things and haven't invested lots of
effort into populating them. Several people have given you good advice.
It's possible to do useful work with poorly designed Tables, but it
takes (maybe much) more effort than necessary.

The self-joins that jacksonmacd and maybe others have suggested
shouldn't be that scary, especially if you're working in Query Design
View. You can even rename the extra views of your Table -- just
right-click on the Table in the upper portion, choose Properties, and
enter a new name into the Alias field. It's still a reference to the
same Table, but it looks in Query Design View like a different Table.

You mentioned that you want to keep things simple. That's a good idea;
as mnature suggested, tracking all possible relationships (maybe to a
dozen generations) will take work and maybe be unnecessary. One virtue
of a RDBMS like Access is that it's pretty flexible. You can start with
a simple model, get it working, then as you discover that you need to
model additional information, it's often not difficult to extend your
original design to take care of that. I've never had to throw away an
Access database because of its becoming unmaintainable. (I have,
however, had to throw away Tables and other objects at times... but
that's not quite as drastic.)

Anyway, I have a few minutes, so I'll try to post another message soon
showing you a bit more graphically what others & I are trying to suggest
that you do.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Yeah i know about referential intergrity. so, you are saying that
relationships are absolutely imperative for the database design, right!!
Please look at http://members.westnet.com.au/mukund/rel.gif and tell if if
there is anything wrong with the way the database is designed (Apart form the
fact that i will be storing simillar records in different tables)

PS: if you want a clearer picture let me know, i can make another one

Regards,

kingnothing

Dear Kingnothing:

Putting the relationships into the table design is an essential. There is
something called referential integrity.

If you have a one-to-many relationship you must not allow the rows on the
many side to be "orphaned". That means, you cannot delete a row in the
table on the "one" side when there are rows on the "many" side. You must
either delete them, too, or prohibit deleting the "one". That's the job of
referential integrity. Try it! You'll see what I mean.

So then, without enforcing referential integrity, there is little likelihood
the database will work well later when you write those queries.

Tom Ellison

Please see inline..

[quoted text clipped - 51 lines]

kingnothing

 

[Tom Ellison]

Dear Kingnothing:

I have been looking at this site. Would you consider making it more useful?

Please make the width and height of the table rectangles large enough to be
able to see the width and height for all the columns and their names.

What I said about combining contact, spouse, and child into a single table
still stands, tentatively, as I have not really seen your design yet.
Please reveal it as I requested and post back here if you're interested.

Tom Ellison

Yeah i know about referential intergrity. so, you are saying that
relationships are absolutely imperative for the database design, right!!
Please look at http://members.westnet.com.au/mukund/rel.gif and tell if if
there is anything wrong with the way the database is designed (Apart form
the
fact that i will be storing simillar records in different tables)

PS: if you want a clearer picture let me know, i can make another one

Regards,

kingnothing

Dear Kingnothing:

Putting the relationships into the table design is an essential. There is
something called referential integrity.

If you have a one-to-many relationship you must not allow the rows on the
many side to be "orphaned". That means, you cannot delete a row in the
table on the "one" side when there are rows on the "many" side. You must
either delete them, too, or prohibit deleting the "one". That's the job
of
referential integrity. Try it! You'll see what I mean.

So then, without enforcing referential integrity, there is little
likelihood
the database will work well later when you write those queries.

Tom Ellison

Please see inline..

[quoted text clipped - 51 lines]

kingnothing