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
|
|||
|
|||
designing for several many-many relationships
Hi database experts,
I'm designing a db to store client personal and business information, and I'm trying to figure out how to best design the forms for input. I've got several many-to-many relationships that I broke down into one-to-many tables by use of an associated link table. I can post a jpeg of the relationships, but I don't think this group allows that. Everything is 3rd order normal, so I'm fine there. It's planning for efficiency and ease of queries and forms that I'm not sure about. Essentially my relationships look like a daisy chain as follows: tblClients (1-oo) tblClient_Parents_LINK (1-oo) tblParents (1-oo) tblParent_Child_LINK (1-oo) tblChildren (1-oo) tblCollege_Child_LINK (1-oo) tblColleges where (1-oo) means One-to-many, if it wasn't clear. Each of the LINK tables have 2 fields, an ID field to match each of the tables. (e.g. tblClient_Parents_LINK has ClientID and ParentID fields) A client consists of a set of parents and children. Parents can have many children, and Children can have multiple parents, including biological, step, and adoptive. One question is whether Children should be directly related to Parents (as they are now) or directly related to the Client. If my main form is a Client entry form, I will need to enter the parents and children that represent the client. Can this be done with a standard query, or will VBA code be needed? I've looked in a number of books, but never found this situation precisely. Is there a book that addresses precisely this situation? I know I can have the Client form based on the Client table, and then a subform based on the LINK table, but how do I enter parents and children? What should I enter first? How can parents and children be automatically associated with each other? Can this be done via a well designed query so that 2 tables are update at the same time? How do I do this as efficiently (& as painlessly) as possible? Thanks! Jay |
#2
|
|||
|
|||
designing for several many-many relationships
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which illustrates a couple of ways to do this. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Jay Wolfe" wrote in message ... Hi database experts, I'm designing a db to store client personal and business information, and I'm trying to figure out how to best design the forms for input. I've got several many-to-many relationships that I broke down into one-to-many tables by use of an associated link table. I can post a jpeg of the relationships, but I don't think this group allows that. Everything is 3rd order normal, so I'm fine there. It's planning for efficiency and ease of queries and forms that I'm not sure about. Essentially my relationships look like a daisy chain as follows: tblClients (1-oo) tblClient_Parents_LINK (1-oo) tblParents (1-oo) tblParent_Child_LINK (1-oo) tblChildren (1-oo) tblCollege_Child_LINK (1-oo) tblColleges where (1-oo) means One-to-many, if it wasn't clear. Each of the LINK tables have 2 fields, an ID field to match each of the tables. (e.g. tblClient_Parents_LINK has ClientID and ParentID fields) A client consists of a set of parents and children. Parents can have many children, and Children can have multiple parents, including biological, step, and adoptive. One question is whether Children should be directly related to Parents (as they are now) or directly related to the Client. If my main form is a Client entry form, I will need to enter the parents and children that represent the client. Can this be done with a standard query, or will VBA code be needed? I've looked in a number of books, but never found this situation precisely. Is there a book that addresses precisely this situation? I know I can have the Client form based on the Client table, and then a subform based on the LINK table, but how do I enter parents and children? What should I enter first? How can parents and children be automatically associated with each other? Can this be done via a well designed query so that 2 tables are update at the same time? How do I do this as efficiently (& as painlessly) as possible? Thanks! Jay |
#3
|
|||
|
|||
designing for several many-many relationships
"Roger Carlson" wrote in message ... On my website (www.rogersaccesslibrary.com), is a small Access database sample called "ImplementingM2MRelationship.mdb" which illustrates a couple of ways to do this. Hi, Thanks for the reply. I took a look at your db and it is similar to a couple I've seen (Including Northwind). The problem I'm having is with the daisy chain, since between Client and Children there are 3 tables. I'm not sure if that's the most efficient use or if that works for forms or not. Are there any other db's you have that illustrate this more complex structure? Thanks! |
#4
|
|||
|
|||
designing for several many-many relationships
Sorry. I didn't read closely enough. The only other sample that might be
close is SubForm3Levels.mdb. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Jay Wolfe" wrote in message ... "Roger Carlson" wrote in message ... On my website (www.rogersaccesslibrary.com), is a small Access database sample called "ImplementingM2MRelationship.mdb" which illustrates a couple of ways to do this. Hi, Thanks for the reply. I took a look at your db and it is similar to a couple I've seen (Including Northwind). The problem I'm having is with the daisy chain, since between Client and Children there are 3 tables. I'm not sure if that's the most efficient use or if that works for forms or not. Are there any other db's you have that illustrate this more complex structure? Thanks! |
#5
|
|||
|
|||
designing for several many-many relationships
With your current structure, what will happen once a Child becomes a Parent in their own right, or what happens if a parent attends college? Looks to me like your database design won't handle these situations. Have you considered using a single "People" table instead of the separate Parents and Children tables? Parents and children from the single table would be related to one another using a "self join". Then have a "RelationshipType" table to describe the type of relationship between the two people "IsBiologicalParentOf", "IsAdoptiveParentOf", "IsStepParentOf", etc. IOW, the junction table would have three fields: ParentID (ie, a PK from the People table), ChildID (also a PK from the People table), and a RelationshipTypeID. Just my $0.02 -- I haven't modelled such a relationship. On Fri, 17 Aug 2007 07:56:37 -0700, "Jay Wolfe" wrote: Hi database experts, I'm designing a db to store client personal and business information, and I'm trying to figure out how to best design the forms for input. I've got several many-to-many relationships that I broke down into one-to-many tables by use of an associated link table. I can post a jpeg of the relationships, but I don't think this group allows that. Everything is 3rd order normal, so I'm fine there. It's planning for efficiency and ease of queries and forms that I'm not sure about. Essentially my relationships look like a daisy chain as follows: tblClients (1-oo) tblClient_Parents_LINK (1-oo) tblParents (1-oo) tblParent_Child_LINK (1-oo) tblChildren (1-oo) tblCollege_Child_LINK (1-oo) tblColleges where (1-oo) means One-to-many, if it wasn't clear. Each of the LINK tables have 2 fields, an ID field to match each of the tables. (e.g. tblClient_Parents_LINK has ClientID and ParentID fields) A client consists of a set of parents and children. Parents can have many children, and Children can have multiple parents, including biological, step, and adoptive. One question is whether Children should be directly related to Parents (as they are now) or directly related to the Client. If my main form is a Client entry form, I will need to enter the parents and children that represent the client. Can this be done with a standard query, or will VBA code be needed? I've looked in a number of books, but never found this situation precisely. Is there a book that addresses precisely this situation? I know I can have the Client form based on the Client table, and then a subform based on the LINK table, but how do I enter parents and children? What should I enter first? How can parents and children be automatically associated with each other? Can this be done via a well designed query so that 2 tables are update at the same time? How do I do this as efficiently (& as painlessly) as possible? Thanks! Jay -- remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
Thread Tools | |
Display Modes | |
|
|