Convoluted many-to-many relationship

  • Thread starter Thread starter Tal
  • Start date Start date
T

Tal

Hello,

I am getting caught up in a big logic circle and I need some help to resolve
the table and relationship structure.

I work for a charity. We have personal, family and corporate donors.
Personal donors may belong to families and to corporations, etc. So I would
like to have one client table that list all possible entities and then create
a junction table that defines relationships between clients.

Here 's where it gets kind of tricky. My clients may have multiple
addresses. And the same address might be shared by multiple clients (although
given the users, they enter the same address in anyway) so that is less
important.

However, how do I structure these 2 many-to-many relationships, given that I
would like each client to automatically "inherit" it's client connection
addresses.

For example:

Client 1 - Home Address 1
Client 1 - Work Address 1

Client 2 connected to Client 1

Client 2 - Home Address 1
Client 2 - Work Address 1

Help!! I am caught in my own crazy logic!!

Tal.
 
I'm about to start building a client database for a gym. The approach that
I'm looking at is to view everything in terms of Households and Household
members. The Household will be an address, the Household members will be the
clients that belong to that household. In terms of forms, the Household
information will be on the main form with the household members being in the
subform. Something like this might work for you. Obviously 'Household' and
'Household Members' won't work in terms of terminology. Based on my approach
the tables would be...

tblHouseholds
(Contains address information)

tblClients
(Contains client names, emails, etc.)

tblHouseholdMembers
(Ties clients to the households allowing a client to belong to more than 1
household)
Sample Fields
PK Household Key Client Key

In your situation where a donor could be a family, person or corporation,
I'd probably go with the following

tblDonorOrganizations
Contains address information on the donor, donor type (Family, Individual,
Business), company name (if applicable), main contact number

tblDonors
Contains the names & contact information for the specific donors

[uncertain of table name]
Associates Donors with their respective Donor Organizations

Doese that help?
 
Actually yes, but the question is thus.

Let's say I have an individual donor, so he is just in the regular client
table with a one to 2 relationship with the Address table and then he gets
married.
So, now I create a "Household" from this client and in ClientGroup table.
How does the Household entity inherit the individual's addresses in a way
that my users don't have to see it happen.

Thanks,
Tal

David H said:
I'm about to start building a client database for a gym. The approach that
I'm looking at is to view everything in terms of Households and Household
members. The Household will be an address, the Household members will be the
clients that belong to that household. In terms of forms, the Household
information will be on the main form with the household members being in the
subform. Something like this might work for you. Obviously 'Household' and
'Household Members' won't work in terms of terminology. Based on my approach
the tables would be...

tblHouseholds
(Contains address information)

tblClients
(Contains client names, emails, etc.)

tblHouseholdMembers
(Ties clients to the households allowing a client to belong to more than 1
household)
Sample Fields
PK Household Key Client Key

In your situation where a donor could be a family, person or corporation,
I'd probably go with the following

tblDonorOrganizations
Contains address information on the donor, donor type (Family, Individual,
Business), company name (if applicable), main contact number

tblDonors
Contains the names & contact information for the specific donors

[uncertain of table name]
Associates Donors with their respective Donor Organizations

Doese that help?


Tal said:
Hello,

I am getting caught up in a big logic circle and I need some help to resolve
the table and relationship structure.

I work for a charity. We have personal, family and corporate donors.
Personal donors may belong to families and to corporations, etc. So I would
like to have one client table that list all possible entities and then create
a junction table that defines relationships between clients.

Here 's where it gets kind of tricky. My clients may have multiple
addresses. And the same address might be shared by multiple clients (although
given the users, they enter the same address in anyway) so that is less
important.

However, how do I structure these 2 many-to-many relationships, given that I
would like each client to automatically "inherit" it's client connection
addresses.

For example:

Client 1 - Home Address 1
Client 1 - Work Address 1

Client 2 connected to Client 1

Client 2 - Home Address 1
Client 2 - Work Address 1

Help!! I am caught in my own crazy logic!!

Tal.
Click to expand...
Click to expand...
 
Hi Tal,
there is a sample database that shows one way to solve this problem:
It's called 'people in households and companies'

http://allenbrowne.com/AppHuman.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Tal said:
Actually yes, but the question is thus.

Let's say I have an individual donor, so he is just in the regular client
table with a one to 2 relationship with the Address table and then he gets
married.
So, now I create a "Household" from this client and in ClientGroup table.
How does the Household entity inherit the individual's addresses in a way
that my users don't have to see it happen.

Thanks,
Tal

David H said:
I'm about to start building a client database for a gym. The approach
that
I'm looking at is to view everything in terms of Households and Household
members. The Household will be an address, the Household members will be
the
clients that belong to that household. In terms of forms, the Household
information will be on the main form with the household members being in
the
subform. Something like this might work for you. Obviously 'Household'
and
'Household Members' won't work in terms of terminology. Based on my
approach
the tables would be...

tblHouseholds
(Contains address information)

tblClients
(Contains client names, emails, etc.)

tblHouseholdMembers
(Ties clients to the households allowing a client to belong to more than
1
household)
Sample Fields
PK Household Key Client Key

In your situation where a donor could be a family, person or corporation,
I'd probably go with the following

tblDonorOrganizations
Contains address information on the donor, donor type (Family,
Individual,
Business), company name (if applicable), main contact number

tblDonors
Contains the names & contact information for the specific donors

[uncertain of table name]
Associates Donors with their respective Donor Organizations

Doese that help?


Tal said:
Hello,

I am getting caught up in a big logic circle and I need some help to
resolve
the table and relationship structure.

I work for a charity. We have personal, family and corporate donors.
Personal donors may belong to families and to corporations, etc. So I
would
like to have one client table that list all possible entities and then
create
a junction table that defines relationships between clients.

Here 's where it gets kind of tricky. My clients may have multiple
addresses. And the same address might be shared by multiple clients
(although
given the users, they enter the same address in anyway) so that is less
important.

However, how do I structure these 2 many-to-many relationships, given
that I
would like each client to automatically "inherit" it's client
connection
addresses.

For example:

Client 1 - Home Address 1
Client 1 - Work Address 1

Client 2 connected to Client 1

Client 2 - Home Address 1
Client 2 - Work Address 1

Help!! I am caught in my own crazy logic!!

Tal.
Click to expand...
Click to expand...
Click to expand...
 
You would remove the person from the one household and assign them to the new
household. Since the relationship would be one-to-many, you'd actually delete
the record that ties the person to the old household and create a new record
tieing them to the new one. Keep in mind that the way that I've envisioned
this for my current project defines the Household as simply an address with
the Household Members being child records. (Its also all theoretical as I
haven't started developing it yet, just designing it.) To streamline it, you
may need some sort of popup window that allows you to 'move' the person from
the current household to a new one. The popup window would display a list of
households, the user would then select the new one. Any donations from an
individual would have to be tied directly to the Household Member so that
they can follow the person as he/she is moved from household to household.

(Also see my note below)

Tal said:
Actually yes, but the question is thus.

Let's say I have an individual donor, so he is just in the regular client
table with a one to 2 relationship with the Address table and then he gets
married.
So, now I create a "Household" from this client and in ClientGroup table.
How does the Household entity inherit the individual's addresses in a way
that my users don't have to see it happen.
Click to expand...
The table that contains the peoples would not capture the address, only
person specific information such as email, phone, etc. The address would be
in the Household table. Its the address that defines the household. Think of
it as building the house first - it goes down on a plot of land, gets the
address, its buildt and then the people move in. (until their ruthlessly
thrown out because the home's been foreclosed)
Thanks,
Tal

David H said:
I'm about to start building a client database for a gym. The approach that
I'm looking at is to view everything in terms of Households and Household
members. The Household will be an address, the Household members will be the
clients that belong to that household. In terms of forms, the Household
information will be on the main form with the household members being in the
subform. Something like this might work for you. Obviously 'Household' and
'Household Members' won't work in terms of terminology. Based on my approach
the tables would be...

tblHouseholds
(Contains address information)

tblClients
(Contains client names, emails, etc.)

tblHouseholdMembers
(Ties clients to the households allowing a client to belong to more than 1
household)
Sample Fields
PK Household Key Client Key

In your situation where a donor could be a family, person or corporation,
I'd probably go with the following

tblDonorOrganizations
Contains address information on the donor, donor type (Family, Individual,
Business), company name (if applicable), main contact number

tblDonors
Contains the names & contact information for the specific donors

[uncertain of table name]
Associates Donors with their respective Donor Organizations

Doese that help?


Tal said:
Hello,

I am getting caught up in a big logic circle and I need some help to resolve
the table and relationship structure.

I work for a charity. We have personal, family and corporate donors.
Personal donors may belong to families and to corporations, etc. So I would
like to have one client table that list all possible entities and then create
a junction table that defines relationships between clients.

Here 's where it gets kind of tricky. My clients may have multiple
addresses. And the same address might be shared by multiple clients (although
given the users, they enter the same address in anyway) so that is less
important.

However, how do I structure these 2 many-to-many relationships, given that I
would like each client to automatically "inherit" it's client connection
addresses.

For example:

Client 1 - Home Address 1
Client 1 - Work Address 1

Client 2 connected to Client 1

Client 2 - Home Address 1
Client 2 - Work Address 1

Help!! I am caught in my own crazy logic!!

Tal.
Click to expand...
Click to expand...
Click to expand...
 
What! he stole my idea and design! even though I came up with it back in the
late 90's for another project and never actually published it or discussed it
with anyone else - he must have a PI stalking me. :)

Jeanette Cunningham said:
Hi Tal,
there is a sample database that shows one way to solve this problem:
It's called 'people in households and companies'

http://allenbrowne.com/AppHuman.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Tal said:
Actually yes, but the question is thus.

Let's say I have an individual donor, so he is just in the regular client
table with a one to 2 relationship with the Address table and then he gets
married.
So, now I create a "Household" from this client and in ClientGroup table.
How does the Household entity inherit the individual's addresses in a way
that my users don't have to see it happen.

Thanks,
Tal

David H said:
I'm about to start building a client database for a gym. The approach
that
I'm looking at is to view everything in terms of Households and Household
members. The Household will be an address, the Household members will be
the
clients that belong to that household. In terms of forms, the Household
information will be on the main form with the household members being in
the
subform. Something like this might work for you. Obviously 'Household'
and
'Household Members' won't work in terms of terminology. Based on my
approach
the tables would be...

tblHouseholds
(Contains address information)

tblClients
(Contains client names, emails, etc.)

tblHouseholdMembers
(Ties clients to the households allowing a client to belong to more than
1
household)
Sample Fields
PK Household Key Client Key

In your situation where a donor could be a family, person or corporation,
I'd probably go with the following

tblDonorOrganizations
Contains address information on the donor, donor type (Family,
Individual,
Business), company name (if applicable), main contact number

tblDonors
Contains the names & contact information for the specific donors

[uncertain of table name]
Associates Donors with their respective Donor Organizations

Doese that help?


:

Hello,

I am getting caught up in a big logic circle and I need some help to
resolve
the table and relationship structure.

I work for a charity. We have personal, family and corporate donors.
Personal donors may belong to families and to corporations, etc. So I
would
like to have one client table that list all possible entities and then
create
a junction table that defines relationships between clients.

Here 's where it gets kind of tricky. My clients may have multiple
addresses. And the same address might be shared by multiple clients
(although
given the users, they enter the same address in anyway) so that is less
important.

However, how do I structure these 2 many-to-many relationships, given
that I
would like each client to automatically "inherit" it's client
connection
addresses.

For example:

Client 1 - Home Address 1
Client 1 - Work Address 1

Client 2 connected to Client 1

Client 2 - Home Address 1
Client 2 - Work Address 1

Help!! I am caught in my own crazy logic!!

Tal.
Click to expand...
Click to expand...
Click to expand...
 
What! he stole my idea and design! even though I came up with it back in the
late 90's for another project and never actually published it or discussed it
with anyone else - he must have a PI stalking me. :)
Click to expand...

Hey! Me too!! Same logic in my church database "MoveOut" button (moving a
person out of a family).

actually "Great minds run in the same channels."

Unfortunately it's also true that "little minds run in the same gutters..."
 
Hi David and Jeanette,

Thank you for your suggestions. I am relying on the Allen Browne's design to
a large extent, though I can't define a Household as an address. Anyway, I am
deeply embroiled in trying to create the table structure at the moment. I
will post it for suggestions once I have something cogent.

Thanks so much for your help.

Cheers,
Tal

David H said:
You would remove the person from the one household and assign them to the new
household. Since the relationship would be one-to-many, you'd actually delete
the record that ties the person to the old household and create a new record
tieing them to the new one. Keep in mind that the way that I've envisioned
this for my current project defines the Household as simply an address with
the Household Members being child records. (Its also all theoretical as I
haven't started developing it yet, just designing it.) To streamline it, you
may need some sort of popup window that allows you to 'move' the person from
the current household to a new one. The popup window would display a list of
households, the user would then select the new one. Any donations from an
individual would have to be tied directly to the Household Member so that
they can follow the person as he/she is moved from household to household.

(Also see my note below)

Tal said:
Actually yes, but the question is thus.

Let's say I have an individual donor, so he is just in the regular client
table with a one to 2 relationship with the Address table and then he gets
married.
So, now I create a "Household" from this client and in ClientGroup table.
How does the Household entity inherit the individual's addresses in a way
that my users don't have to see it happen.
Click to expand...
The table that contains the peoples would not capture the address, only
person specific information such as email, phone, etc. The address would be
in the Household table. Its the address that defines the household. Think of
it as building the house first - it goes down on a plot of land, gets the
address, its buildt and then the people move in. (until their ruthlessly
thrown out because the home's been foreclosed)
Thanks,
Tal

David H said:
I'm about to start building a client database for a gym. The approach that
I'm looking at is to view everything in terms of Households and Household
members. The Household will be an address, the Household members will be the
clients that belong to that household. In terms of forms, the Household
information will be on the main form with the household members being in the
subform. Something like this might work for you. Obviously 'Household' and
'Household Members' won't work in terms of terminology. Based on my approach
the tables would be...

tblHouseholds
(Contains address information)

tblClients
(Contains client names, emails, etc.)

tblHouseholdMembers
(Ties clients to the households allowing a client to belong to more than 1
household)
Sample Fields
PK Household Key Client Key

In your situation where a donor could be a family, person or corporation,
I'd probably go with the following

tblDonorOrganizations
Contains address information on the donor, donor type (Family, Individual,
Business), company name (if applicable), main contact number

tblDonors
Contains the names & contact information for the specific donors

[uncertain of table name]
Associates Donors with their respective Donor Organizations

Doese that help?


:

Hello,

I am getting caught up in a big logic circle and I need some help to resolve
the table and relationship structure.

I work for a charity. We have personal, family and corporate donors.
Personal donors may belong to families and to corporations, etc. So I would
like to have one client table that list all possible entities and then create
a junction table that defines relationships between clients.

Here 's where it gets kind of tricky. My clients may have multiple
addresses. And the same address might be shared by multiple clients (although
given the users, they enter the same address in anyway) so that is less
important.

However, how do I structure these 2 many-to-many relationships, given that I
would like each client to automatically "inherit" it's client connection
addresses.

For example:

Client 1 - Home Address 1
Client 1 - Work Address 1

Client 2 connected to Client 1

Client 2 - Home Address 1
Client 2 - Work Address 1

Help!! I am caught in my own crazy logic!!

Tal.
Click to expand...
Click to expand...
Click to expand...
 
From a design stand point get a legal pad and Pilot G2 Pen* and draw out the
processes and the relationships using real world terminology without thinking
in terms of tables, fields, etc. I always do something like this to envision
how its going to work before ever creating a single table as it lays out the
final destination and often form design. Think in terms of drawing diagrams
and form mock-ups that serve to tell the story of how the database works.

David H

*PS This (as well as most things) will only work with a G2 Pen.

Tal said:
Hi David and Jeanette,

Thank you for your suggestions. I am relying on the Allen Browne's design to
a large extent, though I can't define a Household as an address. Anyway, I am
deeply embroiled in trying to create the table structure at the moment. I
will post it for suggestions once I have something cogent.

Thanks so much for your help.

Cheers,
Tal

David H said:
You would remove the person from the one household and assign them to the new
household. Since the relationship would be one-to-many, you'd actually delete
the record that ties the person to the old household and create a new record
tieing them to the new one. Keep in mind that the way that I've envisioned
this for my current project defines the Household as simply an address with
the Household Members being child records. (Its also all theoretical as I
haven't started developing it yet, just designing it.) To streamline it, you
may need some sort of popup window that allows you to 'move' the person from
the current household to a new one. The popup window would display a list of
households, the user would then select the new one. Any donations from an
individual would have to be tied directly to the Household Member so that
they can follow the person as he/she is moved from household to household.

(Also see my note below)

Tal said:
Actually yes, but the question is thus.

Let's say I have an individual donor, so he is just in the regular client
table with a one to 2 relationship with the Address table and then he gets
married.
So, now I create a "Household" from this client and in ClientGroup table.
How does the Household entity inherit the individual's addresses in a way
that my users don't have to see it happen.
Click to expand...
The table that contains the peoples would not capture the address, only
person specific information such as email, phone, etc. The address would be
in the Household table. Its the address that defines the household. Think of
it as building the house first - it goes down on a plot of land, gets the
address, its buildt and then the people move in. (until their ruthlessly
thrown out because the home's been foreclosed)
Thanks,
Tal

:

I'm about to start building a client database for a gym. The approach that
I'm looking at is to view everything in terms of Households and Household
members. The Household will be an address, the Household members will be the
clients that belong to that household. In terms of forms, the Household
information will be on the main form with the household members being in the
subform. Something like this might work for you. Obviously 'Household' and
'Household Members' won't work in terms of terminology. Based on my approach
the tables would be...

tblHouseholds
(Contains address information)

tblClients
(Contains client names, emails, etc.)

tblHouseholdMembers
(Ties clients to the households allowing a client to belong to more than 1
household)
Sample Fields
PK Household Key Client Key

In your situation where a donor could be a family, person or corporation,
I'd probably go with the following

tblDonorOrganizations
Contains address information on the donor, donor type (Family, Individual,
Business), company name (if applicable), main contact number

tblDonors
Contains the names & contact information for the specific donors

[uncertain of table name]
Associates Donors with their respective Donor Organizations

Doese that help?


:

Hello,

I am getting caught up in a big logic circle and I need some help to resolve
the table and relationship structure.

I work for a charity. We have personal, family and corporate donors.
Personal donors may belong to families and to corporations, etc. So I would
like to have one client table that list all possible entities and then create
a junction table that defines relationships between clients.

Here 's where it gets kind of tricky. My clients may have multiple
addresses. And the same address might be shared by multiple clients (although
given the users, they enter the same address in anyway) so that is less
important.

However, how do I structure these 2 many-to-many relationships, given that I
would like each client to automatically "inherit" it's client connection
addresses.

For example:

Client 1 - Home Address 1
Client 1 - Work Address 1

Client 2 connected to Client 1

Client 2 - Home Address 1
Client 2 - Work Address 1

Help!! I am caught in my own crazy logic!!

Tal.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top