Multiple Many to many relationships

[Guest]

I have a database which at the moment contains two tables. I have a franchise
table which has details of our various franchise outlets. Each franchise may
have one or more; owners, guarantors, operating entities, nominated managers
or all of them.

I then have a customer table which provides the details of all of our
customers. A customer may be an owner, manager, guarantor, entity etc. A
customer may be the owner of more than one franchise.

So I have many fields from the customer table which I need to relate to the
franchise table with a many to many relationship on both sides - how do I
manage this ???

 

[Jeff Boyce]

To resolve two many-to-many tables, add a another table. This third table
will hold valid pairs of the rowIDs from table one and table two. You may
find more information about this in Access HELP, and in Google.com,
searching for many-to-many, "join table", "relation table", "resolver
table".

 

[Tim Ferguson]

So I have many fields from the customer table which I need to relate
to the franchise table with a many to many relationship on both sides
- how do I manage this ???

Just to amplify Jeff's response, the third table also needs a field
describing the nature of the relationship e.g.

Outlet Customer Relationship
===== ======== ------------
Belco David Owner
Belco Eric Guarantor
Belco Fred Operator
Chunki Gavin Owner
Chunki Eric Guarantor
Chunki Harold Operator
Chunki Iain Operator


The choice of key for this table depends on the business rules. If a
Customer can have just one relationship with the same outlet, then the PK
should be (Outlet, Customer). On the other hand, if David can be an
Operator and an Owner of Belco, then the PK has to be made up of all
three fields.

You might also want to keep details of when the relationship started;
whether it's historic or current; contracted or subcontracted; etc. All
these would be fields in the same table.

Hope that helps


Tim F

 

[Guest]

Thanks to the both of you for your responses. I still havent quite got my
head around it.

I am aware of the use of junction tables to which I think you are both
referring. However to use Tim's example: David may be the owner of Belco, but
may also be the guarantor of Belco. Also Belco may have more than one owner
and more than one guarantor, ,further to this David may also be the owner
and/or manager and/or guarantor of Chunki.

I just cant work out how to create these many to many relationships across
all possible permeatations (did I spell that right?).

Do I create a table with the customer personal details etc and include
fields of entityid, ownerid, managerid and guarantor id and then somehow link
them to the franchise table that has a franchiseid, but how do I create a
relationship accross so many fields in one table ???

Any help is appreciated.

 

[Tim Ferguson]

I am aware of the use of junction tables to which I think you are both
referring. However to use Tim's example: David may be the owner of
Belco, but may also be the guarantor of Belco. Also Belco may have
more than one owner and more than one guarantor, ,further to this
David may also be the owner and/or manager and/or guarantor of Chunki.

No problem: each of these facts deserves one row in the IsConnectedWith
table e.g.:

David, Belco, Owner
David, Belco, Guarantor
Eric, Belco, Owner
Frances, Belco, Guarantor
David, Chunki, Owner
David, Chunki, Manager
David, Chunki, Guarantor

In this case, the primary key would consist of all three fields (because
there is no sense in storing "Gavin, Delice, Owner" more than once). If
this layout looks funny, remember that 'records are cheap, columns are
expensive". Imagine how much easier it is to find all the Franchises that
David is connected with:

SELECT Outlet, Relationship
FROM IsConnectedWith
WHERE Customer = "David"

rather than

SELECT Outlet FROM Franchises
WHERE Owner1 = "David" OR Owner2="David" OR Owner2 = "David"
OR Guarantor1 = "David" OR Guarantor2 = "David"
OR Manager1 = "David" OR Manager2 = "David" OR Manager2 = "David"
OR etc etc etc

and don't even think about trying to get outlets where David and Frances
work together...

To get the managers for Belco, you query something like

SELECT Customer FROM IsConnectedWith
WHERE Outlet = "Belco" AND Relationship = "Manager"

Do I create a table with the customer personal details etc and include
fields of entityid, ownerid, managerid and guarantor id and then
somehow link them to the franchise table that has a franchiseid, but
how do I create a relationship accross so many fields in one table ???

You don't -- see above. You need (currently) three tables:

Outlets(*OutletCode, FullName, PostalAddress, etc etc)

Customers(*CustomerID, FName, LName, etc, etc)

IsConnectedWith(OutletCode, CustomerID, Relationship)

In reality, I would actually add one more table for control:

Relationships(*Code, Description)

which would have records containing values like "1, Owner", "2, Manager",
"3, Guarantor" and so on, to prevent illegal or meaningless things like
"Ooner" appearing in the IsConnectedWith table.

I hope that makes some kind of sense!
B Wishes


Tim F

 

[Guest]

Thank you immensly Tim, Being a relatively new user it took some time to
understand the concept you were trying to establish for me, but now I've got
it! and its a perfect set up that meets my needs perfectly. Cheers. By the
way, is there any chance of DAAS getting back together again?

 

[Tim Ferguson]

Being a relatively new user it took some time to understand the
concept you were trying to establish for me, but now I've got it! and
its a perfect set up that meets my needs perfectly.

Glad to have helped.

By the way, is there any chance of DAAS getting back together again?

Who??

Tim F