Linking two tables via a third table

  • Thread starter Thread starter Contro
  • Start date Start date
C

Contro

Hi guys!

I have a problem. Basically, I have two tables of two different kinds
of customer (a direct applicant, and an applicant from a third party).
These need to be kept separate, and can not be merged into the same
table.

Each kind of customer can make a payment, which is stored in the
payment table. This table is linked to the two customer tables via an
Applicant ID, with Payment ID being the primary key in the payment
table.

The problem is that, when I try to enter any payment details for either
of the customer types, I get an error saying "you cannot add or change
a record because a related record is required [in the other customer
table]"

If I turn off the "enforce referential integrety" rules for the
relationships, then there is not a problem. However, this does not
seem ideal....do any of you know of a better solution; one that is more
"professional"?

Any help would be hugely appreciated!

Contro.
 
The customers should all be in a single table. Use a field with two values
so you can identify which is which.

A less attractive alternative is to create a customer table to hold all the
common fields plus a field that identifies customer type. Then you would
create two additional tables. One for each kind of customer. These
secondary tables would be related 1-1 with the customer table. ALL
relationships involving customer would be made to the parent customer table.
Nothing would ever be related to the customer type tables.
 
Your second solution might be less attractive, but I think it is likely to be
the right one as what Contro seems to have here is a classic Type/Subtype
scenario (its analogous to Chris Date's example of a Programmers type and
System and Application Programmers subtypes). What puzzles me, however, is
why you suggest a column to identify customer type in the Customers table.
The type of customer is determined by the inclusion of a referencing row in
one or other of the subtypes tables, so the inclusion of a column in the
(supertype) Customers table is redundant and could give rise to an update
anomaly. There might also be the possibility of a customer being both
customer (sub)types simultaneously, though Contro's post tends to suggest
this is unlikely; nevertheless it’s a theoretical possibility.

Ken Sheridan
Stafford, England

Pat Hartman(MVP) said:
The customers should all be in a single table. Use a field with two values
so you can identify which is which.

A less attractive alternative is to create a customer table to hold all the
common fields plus a field that identifies customer type. Then you would
create two additional tables. One for each kind of customer. These
secondary tables would be related 1-1 with the customer table. ALL
relationships involving customer would be made to the parent customer table.
Nothing would ever be related to the customer type tables.

Contro said:
Hi guys!

I have a problem. Basically, I have two tables of two different kinds
of customer (a direct applicant, and an applicant from a third party).
These need to be kept separate, and can not be merged into the same
table.

Each kind of customer can make a payment, which is stored in the
payment table. This table is linked to the two customer tables via an
Applicant ID, with Payment ID being the primary key in the payment
table.

The problem is that, when I try to enter any payment details for either
of the customer types, I get an error saying "you cannot add or change
a record because a related record is required [in the other customer
table]"

If I turn off the "enforce referential integrety" rules for the
relationships, then there is not a problem. However, this does not
seem ideal....do any of you know of a better solution; one that is more
"professional"?

Any help would be hugely appreciated!

Contro.
Click to expand...
Click to expand...
 
Thanks for your replies! Yes, I have to admit that I can't believe I
didn't have one main customer table, linking to the other "customer
types" tables, which would have made a lot more sense.

I shall try to implement this when I can. It's a pain when you make
such a simple oversight, but it causes so much hassle later on!

Well, you live and learn. And yes, I think it would be wrong to
include a field requesting the customer type...as you say, that would
be determined without it. But another easy thing to overlook if you
are not careful.

Thanks again!

Contro.
 
Yes the type code does technically violate second normal form but I find
that it simplifies form design because it allows me an easy way to show the
"correct" subform for the type of entity being viewed. It also helps with
building queries in code so you know which table you need to join to. And
finally, it allows you to use left joins rather than inner joins because you
can select the entities you want to view based on their type rather than the
presence of a row in a related table. This allows the related tables to be
sparse. Obviously, if it is possible for an entity to belong to multiple
classes, you can't use this crutch and you have to live with the
presence/absence of rows in a class table to determine to which classes an
entity belongs. And following that logic, the 1-side records now become
required since they would be the only indicator of class.


Ken Sheridan said:
Your second solution might be less attractive, but I think it is likely to
be
the right one as what Contro seems to have here is a classic Type/Subtype
scenario (its analogous to Chris Date's example of a Programmers type and
System and Application Programmers subtypes). What puzzles me, however,
is
why you suggest a column to identify customer type in the Customers table.
The type of customer is determined by the inclusion of a referencing row
in
one or other of the subtypes tables, so the inclusion of a column in the
(supertype) Customers table is redundant and could give rise to an update
anomaly. There might also be the possibility of a customer being both
customer (sub)types simultaneously, though Contro's post tends to suggest
this is unlikely; nevertheless it's a theoretical possibility.

Ken Sheridan
Stafford, England

Pat Hartman(MVP) said:
The customers should all be in a single table. Use a field with two
values
so you can identify which is which.

A less attractive alternative is to create a customer table to hold all
the
common fields plus a field that identifies customer type. Then you would
create two additional tables. One for each kind of customer. These
secondary tables would be related 1-1 with the customer table. ALL
relationships involving customer would be made to the parent customer
table.
Nothing would ever be related to the customer type tables.

Contro said:
Hi guys!

I have a problem. Basically, I have two tables of two different kinds
of customer (a direct applicant, and an applicant from a third party).
These need to be kept separate, and can not be merged into the same
table.

Each kind of customer can make a payment, which is stored in the
payment table. This table is linked to the two customer tables via an
Applicant ID, with Payment ID being the primary key in the payment
table.

The problem is that, when I try to enter any payment details for either
of the customer types, I get an error saying "you cannot add or change
a record because a related record is required [in the other customer
table]"

If I turn off the "enforce referential integrety" rules for the
relationships, then there is not a problem. However, this does not
seem ideal....do any of you know of a better solution; one that is more
"professional"?

Any help would be hugely appreciated!

Contro.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top