Design for multiple Unions

[Dkline]

I have a database design in which there are numerous many to many
relationships.

On the left side is the Case table and is the "Primary" table. CaseID is the
Primary Key.

There are six tables which have a many to many relationship to this table:
Producer, Insured, Beneficiary, Owner, Investment, Allocator

What is the best design for handling six many to many relationships back to
the Case table?

I thought of a Union table with the CaseID and each of the 6 other tables.
But I believe this would not be a good design.

For example:
Tables: Case Producer, Insured, Beneficiary, Owner, Investment,
Allocator
1stRecord: 1 1 1 1 1
1 1
2ndRecord: 1 Null 2 Null Null
Null Null

The current design forces the user to have a record in it that is mostly
Nulls as this case has two insureds but only one of everything else.
Building six separate Union tables seems to be overkill.

Is there a better way?

 

[Ken Snell [MVP]]

I'm not sure I understand what you want to do. If you have six separate
tables that have 1 - to -many relationships each with Case table, what are
you wanting to "accomplish" with the "join" tables? Doesn't each of the six
"child" tables have the value for that table's attribute for each CaseID?

If your intent is to have this "all the tables in one table" is for
displaying the data, then don't do that. Instead, use a query to gather the
information together and display it.

Please provide more details.

 

[Dkline]

There is a many-to-many relationship between the Case table and each of the
other six (now seven) tables.
CaseTable N:N Producer Table
CaseTable N:N BeneficiaryTable
CaseTable N:N OwnerTable
CaseTable N:N InsuredTable
CaseTable N:N InvestmentTable
CaseTable N:N AllocatorTable
CaseTable N:N BDTable (found another table that should be a many-to-many)

A case can have more than once producer albeit it is infrequent. A producer
usually has many cases.

The low tech solution is to set it all up as a one to many with the CaseID
as a foreign key in each of the other tables. But then I'll have duplicated
data which I'm trying to avoid at all costs.

 

[Ken Snell [MVP]]

So your preference is to use a single "junction" table instead of seven
separate junction tables, right? As to whether this is appropriate for your
setup I cannot say -- but there is a way to do this, if you're willing to
possibly have to build queries on the fly in code.

This can be done by using a single "junction" table containing these fields:

CaseID
ChildTableType
ChildTableID

What you do is "assign" a number to each of the children tables, and this
number will be used in ChildTableType field in the above table. For example,
Producer will be a 1, Beneficiary will be a 2, etc. In the junction table,
this identifies which table's record is represented by the junction table's
record.

Then, you put the primary key field's value from the child table into the
ChildTableID field. So you now have a single "junction" table that provides
all the many-to-many relationships.

For example, suppose CaseID of 10 is related to Producer record 2 (primary
key value), and to Producer record 5 (primary key value), and to Beneficiary
record 9 (primary key value). These three specific relationships will show
as three records in the "junction" table:

CaseID ChildTableType ChildTableID
10 1 2
10 1 5
10 2 9

Your queries that get values from the children tables must use this table to
figure out which table and which record in that table is to be "obtained".
The first record says that CaseID has a matching record in the "1" table
(Producers), and that matching record has a primary key value of 2. So your
query needs to know to convert 1 to Producers and then use the 2 to find
that primary key value. This may have to be done in code so that you build
the query to use the correct table.

Alternatively, you could write a query that returns the records from the
junction table for all Producer entries:
SELECT CaseID, ChildTableID
FROM JunctionTable
WHERE ChildTableType = 1;

Then you can use this stored query as a source table for subsequent queries
that are to return the Producer information, as it will tell you which
records in the Producers table match to a specific CaseID value.

So, if you're ready to handle the additional setups, you can go to a single
junction table. High tech? Depends on your point-of-view! But it will work.
--

Ken Snell
<MS ACCESS MVP>

 

[Dkline]

Bear with me. I'm going to implement this and will let you know the results.

 

[Dkline]

It works as advertised. I went down the path of building the separate
queries for now and then running a query of queries to fill the report.
Ultimately I may end up with code.

Thank you for your help.

 

[Ken Snell [MVP]]

Glad to hear that it is working! Good luck.