designing for several many-many relationships

J

Jay Wolfe

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
 
J

Jay Wolfe

Roger Carlson said:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which illustrates a couple
of ways to do this.
Click to expand...


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

jacksonmacd

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dance class 10
How many one to many relationships are too many? 9
How do I set up a many to many relationship with the table analyze 4
Designing a Database 9
Need help with several many to many relationships 8
Multiple Many-to-Many relationships 8
Assistance with Many-to-Many Relationships 4
Where can I download this type of db example? 2

Top