If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convoluted many-to-many relationship
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. |
#2
|
|||
|
|||
Convoluted many-to-many relationship
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" wrote: 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. |
#3
|
|||
|
|||
Convoluted many-to-many relationship
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" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
Convoluted many-to-many relationship
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" wrote in message ... 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" wrote: 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" wrote: 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. |
#5
|
|||
|
|||
Convoluted many-to-many relationship
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" wrote: 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. 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" wrote: 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" wrote: 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. |
#6
|
|||
|
|||
Convoluted many-to-many relationship
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote: 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. |
#7
|
|||
|
|||
Convoluted many-to-many relationship
On Wed, 3 Dec 2008 18:12:05 -0800, David H
wrote: 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. 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..." -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Convoluted many-to-many relationship
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" wrote: 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" wrote: 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. 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" wrote: 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" wrote: 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. |
#9
|
|||
|
|||
Convoluted many-to-many relationship
Hmmmm.... not sure if I'm in a "channel" or a "gutter". g
-- _________ Sean Bailey "John W. Vinson" wrote: On Wed, 3 Dec 2008 18:12:05 -0800, David H wrote: 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. 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..." -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Convoluted many-to-many relationship
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" wrote: 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" wrote: 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" wrote: 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. 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" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|