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

Gerardo Sanchez
  • 43
  • 1
  • 6
  • 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 [mcve]. – philipxy Sep 19 '18 at 19:03

2 Answers2

5

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

Rodolfo Donã Hosp
  • 1,037
  • 1
  • 11
  • 23
  • 1
    This is actually a nice approach. It may be overkill for Gerardo, but it's thinking outside the box. Upvoting. – The Impaler Sep 19 '18 at 17:49
  • The question is a(n obvious) duplicate so should be closed, close voted or duplicate flagged depending on rep, not answered. See [answer] other [help] links. – philipxy Sep 19 '18 at 19:21
  • 1
    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. – Arioch 'The Sep 20 '18 at 09:24
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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • I don't know firebird. But do you think a computed column with a coalesce for those 2 foreign keys would work? – LukStorms Sep 19 '18 at 17:44
  • Thanks, I'd be using this approach to solve the problem – Gerardo Sanchez Sep 19 '18 at 18:26
  • @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?](https://stackoverflow.com/q/3579079/3404097) – philipxy Sep 19 '18 at 19:03