0

I have to model a database table for payments. These need to have two fields payment from and payment to and this needs to link to two tables one is clientA and clientB as in the representation below.

enter image description here

How should I represent in the diagram that payer_from_id and payer_to_id can join on either clientA or clinetB?

clientA and clientB must be in two separate tables.

  • Why would you have 2 client tables? You could conceivably end up with the same client in each table. – DCook Aug 14 '18 at 15:56
  • this is just an example. imagine that the set of attributes in the two tables cannot coexist in one table and that is not possible to have the same client in both tables – Blue Moon Aug 14 '18 at 15:59
0

Yo cannot reference two different tables in a foreign key, so the relationship in the scenario will not be defined in the database. To make it work at all you would need to add [payer_from_table] and [payer_to_table] fields do differentiate between the two. If client #101 is "Moe's Tavern" in clientA but it's "Bob's Burgers" in clientB, you have no way to distinguish between them unless you specify the source table, too.

Another possible solution is to add a GUID [guid_id] field to both tables, which should create a unique identifier for every record regardless of table, assuming the tables are in the same database. You still cannot define a foreign key to two tables at once, but you can index the [guid_id] field in each table to keep it performant. You will not have things like cascading deletes unless you code them yourself.

A third option, which works with either scenario, is to create a third table that is periodically updated with data from both client tables. You would still need either a table identifier or a GUID so they can coexist. Your foreign key could then be defined against the combined table, with a complex key in the first case and against the GUID in the second case.

0

It is not possible to have a multiple table FOREING KEY reference.

The best I can recommend to you is to create an inheritance model: for this, you will add a generic CLIENT table and make a 1 to 1 reference on clientA, B, C, D... or whatever different tables you may need....

However, you have to ensure that there is no repeated IDs on clientA and B. Maybe using a field called: ClientCategory on Client table, and then having a trigger before INSERT to check the category and replicate the ID in the proper table.

The model should look like this:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.