0

I'm using a Firebird database for a software and found a problem

I've got 2 tables, for 2 different types of clients:

clientM( rfcM varchar(12) primary key some other data ) clientF( rfcF varchar(13) primary key some other data )

(length is fixed since it's a standard in México for the data I'm expecting users to input)

Here comes the problem, I need to create a third table:

clientPayment( rfcClient varchar(13) some other data )

and that field must have a foreign key referencing both clientM.rfcM and clientF.rfcF, so I can use the same table to store payments from both types of clients

I could just leave the third table without a foreign key, but was hoping to add one and avoid users inputting incorrect data

Improve this question
1
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on minimal reproducible example. Commented Sep 19, 2018 at 19:03

2 Answers 2

Reset to default
6

In this type of scenario you got two (recommended) options:

  • As The Impaler said, use two columns on your third table, one referencing each table and nullable (because ideally one will always be empty)

  • Or you could use hierarchy between tables, where you would create a table called Client containing all the common data between ClientF and ClientM, and your tables ClientF and ClientM would have the specific data of each type of client, and a ClientF.ID/ClientM.ID refering to it's parent table. And your clientPayment table would have a column referencing the parent table Client

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is actually a nice approach. It may be overkill for Gerardo, but it's thinking outside the box. Upvoting.
The question is a(n obvious) duplicate so should be closed, close voted or duplicate flagged depending on rep, not answered. See How to Answer other help center links.
This second approach seems nice if we have a warranty that data in ClientF and ClientM would never intersect. However if it can (thus sooner or later would) happen, that the ID in both ClientX tables is the same, then the aggregate All-clients table would have troubles. If we can ensure that never happens then it would be my favorite approach here.
1

I highly recommend you use two columns, one per each foreign key. You probably want those columns to be nullable, so a row may "point" to one related table or the other (maybe even none, or both).

It's technically possible to use a single column that stores a foreign key to both related tables. However, you wouldn't be able to point to one or the other at will, since the foreign key constraint will enforce a non-null value to exist in both related tables always. You would need to remove the foreign key constraint, and that's a big no-no.

In short, this is how I see it:

clientPayment( rfcM varchar(12) references clientM, -- nullable by default rfcF varchar(13) references clientF, -- nullable by default constraint one_and_only_one_fk check ( clientM is null and clientF is not null or clientM is not null and clientF is null ), some other data )

The constraint prevents both fks to be null at the same time, or both to be not null at the same time.

Improve this answer

3 Comments

I don't know firebird. But do you think a computed column with a coalesce for those 2 foreign keys would work?
Thanks, I'd be using this approach to solve the problem
@LukStorms This is the right way to constrain those 2 FKs. But the 2 FKs is an anti-pattern for SQL/database subtyping/inheritance. How can you represent inheritance in a database?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.