Design for multiple Unions

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?

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.
--

Ken Snell
MS ACCESS MVP



"Dkline" wrote in message
...
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?

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]" wrote in message
...
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.
--

Ken Snell
MS ACCESS MVP



"Dkline" wrote in message
...
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?

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" wrote in message
...
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]" wrote in message
...
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.
--

Ken Snell
MS ACCESS MVP



"Dkline" wrote in message
...
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?

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

"Ken Snell [MVP]" wrote in message
...
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" wrote in message
...
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]" wrote in message
...
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.
--

Ken Snell
MS ACCESS MVP



"Dkline" wrote in message
...
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?

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.


"Dkline" wrote in message
...
Bear with me. I'm going to implement this and will let you know the
results.

"Ken Snell [MVP]" wrote in message
...
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" wrote in message
...
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]" wrote in message
...
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.
--

Ken Snell
MS ACCESS MVP



"Dkline" wrote in message
...
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?

Glad to hear that it is working! Good luck.

--

Ken Snell
MS ACCESS MVP

"Dkline" wrote in message
...
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.


"Dkline" wrote in message
...
Bear with me. I'm going to implement this and will let you know the
results.

"Ken Snell [MVP]" wrote in message
...
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" wrote in message
...
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]" wrote in message
...
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.
--

Ken Snell
MS ACCESS MVP



"Dkline" wrote in message
...
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?