locked
How to design database when FK has relation with more than one PK? RRS feed

  • Question

  • Dear all

    I have a table that one of the columns in that table should has a possibility to come from two other tables. Let me explain with an example.

    Table T1 has 3 columns. A, B, C. In this table A is Primary Key.

    Table T2 has 4 columns. D, E, F, G. In this table D is Primary Key.

    Table T3 has 3 columns. H, J, K. In this table H is Primary Key and K is Foreign Key. But K can be "T1.A", or T2.D".

     

    This kind of relation is impossible in SQL Server. There are several solution for this that I want to know which is the best.

    1) Using trigger I make a relation that when user want to insert "K" then data for that column have to be valid in "T1.A" or "T2.D". And also for "T1.A" and "T2.D" I will make triggers that if user want to update them it will effect "K" automatically.

    2) Make another table called "T1T2" there I will have 3 columns. "N", "A", and "D". then I connect "T1T2.A" to "T1.A" and "T1T2.D" to "T2.D". So now in table "T3" I will change column "T3.K" to "T3.N" and make relation between "T1T2.K" and "T3.K" . At the end I make a trigger for controlling that in "T1T2" one of the columns "T1T2.A" or "T1T2.D" can have value and the other one must be NULL.

    3) In "T3" I can remove column "T3.K" and then I will add two columns "T3.A" and "T3.D" then make a trigger that only one of them can have a value and the other one must be NULL.

     

    Between these three solutions which one is the best and also why? or maybe there is another solution that I don't know.

     

    Thank you so much for your help.


    Nothing is Impossible
    Wednesday, August 10, 2011 1:24 PM

Answers

  • I see.

    In that case, there should likely be two Check TABLEs. One for Creditor and one for Debtor. Two check TABLEs are nice because it clearly shows Check is a child.

    Your option 3 would also work, but instead of having a TRIGGER enforcing the rule, i would use a CONSTRAINT:
      CHECK((CreditorSource IS NULL AND DebtorSource IS NOT NULL) OR (CreditorSource IS NOT NULL AND DebtorSource IS NULL))

    This is nicer as there is only one Check TABLE, but it is slightly more confusing.

    • Marked as answer by Saman Saadat Friday, August 12, 2011 2:47 AM
    Thursday, August 11, 2011 3:43 PM
    Answerer

All replies

  • To answer your question directly:

    1) It is my belief that TRIGGERs are evil (hide code, hard to maintain, hard to debug, often used instead of a proper solution). Do not use them unless absolutely no other option exists, and even then think twice.
    2) Possible solution, depends on Data Model.
    3) Possible solution, depends on Data Model.

    It is difficult to recommend an answer without understanding the question, and there are two things here obscuring that understanding.

    1) Using letters for TABLE and COLUMN names in something other than a simple example.
    2) Not explaining why the FK refers to more than one TABLE

    By explaining a bit more, we may be able to understand the current Data Model and suggest a new one, which may very well obviate this question, or help differentiate between options 2 and 3, which each are good in some cases.


    Wednesday, August 10, 2011 1:34 PM
    Answerer
  • Dear Brain Tkatch

    Thank you so much for your respond.

    This case happening in so many places in my project. But one example can be this:

    Lets say we have Check table that we save all checks there. These checks can be outgoing or incoming checks. For incoming of the company we have Debtor and for outgoing we have Creditor. These two have it's own tables. Therefore the source for one check can come from Debtor table or from Creditor table. In this case which solution can be the best for this situation?

     

    Thanks

    Best regards


    Nothing is Impossible
    Wednesday, August 10, 2011 2:12 PM
  • 2 sounds like a good option only if, in real life it is logical to have a 3rd table for T1 and T2. I would not recommend designing table just to solve references and have the final design look illogical. Table design should match with logical relationship with objects as much as possible.

    If you really want to solve this at the database level, option 1/3 are the way to go. Otherwise i will recommend implementing the contraint at the application level.

    Wednesday, August 10, 2011 2:43 PM
  • From design standpoint, i would have the Check TABLE be the parent, with type. In each of the Creditor and Debtor TABLEs, have a type COLUMN as well which is forced to be 'Creditor' for Creditor and 'Debtor for Debtor:

    Check_Type  (e.g. Creditor, Debtor)
    --------------
    Name
    Description

    [Check]
    --------
    Routing_Number (PK)(UQ)
    Account_Number (PK)(UQ)
    Type (UQ)(FK: Check_Type)

    Check_Creditor
    ---------
    Routing_Number (PK)(FK: Check)
    Account_Number (PK)(FK: Check)
    Type (FK: Check)
    CONSTRAINT Type = 'Creditor'

    Check_Debtor
    ---------
    Routing_Number (PK)(FK: Check)
    Account_Number (PK)(FK: Check)
    Type (FK: Check)
    CONSTRAINT Type = 'Debtor'


    • Proposed as answer by Mr. Wharty Wednesday, August 10, 2011 11:45 PM
    • Unproposed as answer by Saman Saadat Thursday, August 11, 2011 2:22 AM
    Wednesday, August 10, 2011 3:11 PM
    Answerer
  • Dear Brain Tkatch

    Sorry I think it was my fault, cause I forget to say that Creditor and Debtor have to be parent. Because one debtor can have more than one payment. so he can have more than one check. And also one debtor can pay, each time of his payment in different ways that one way is check. Same situation is happening for Creditor.

     

    Again thanks :)


    Nothing is Impossible
    Wednesday, August 10, 2011 3:28 PM
  • Dear sambeet

    Thanks for your reply.

    Each of these three solutions that I said have it's own benefit and difficulty. I have this problem in many cases. In some cases the FK is not only in one table but it is in 3 to 4 tables. Lets say we have another table T4 with same design of T3. so in that case solution 2 has the advantage of connecting different tables using only one table. It means T1T2 play as a connector table between T3, T4 and T1, T2.

    Do you agree with me? or still think 1/3 is the best? also in 1/3 we will have so many Null values in T3. Does it effect or no?

     

    Again thanks :)


    Nothing is Impossible
    Wednesday, August 10, 2011 3:37 PM
  • That is fine. Just make Check it's own TABLE. That is, Creditor and Debtor each refer to an account that has payments and debts, each check is either or, which can then be applied to one or the other. The question is, how to apply in one and not the other.

    The same suggestion applies, to have the Check TABLE account for which one.The idea is the FK includes a second COLUMN which forces it into one TABLE or the other.

    If you think this does not fit, please post an example or the DDL for the three TABLEs, Creditor, Debtor, and Check.

     


    • Proposed as answer by Mr. Wharty Wednesday, August 10, 2011 11:45 PM
    • Unproposed as answer by Saman Saadat Thursday, August 11, 2011 2:21 AM
    Wednesday, August 10, 2011 4:51 PM
    Answerer
  • Hi Brain Tkatch

    Sorry I cannot understand what you mean.

    ok I give more complete example. Here is my 2/3 solution to solve this problem:

     

    Person

    --------------------

    PersonID (P.K)

    FirstName

    LastName

    MiddleName

     

     

    Creditor

    --------------------

    CreditorID (P.K)

    PersonID(FK: Person)

     

     

    Debtor

    --------------------

    DebtorID (P.K)

    PersonID(FK: Person)

     

     

    DebtorCreditor

    --------------------

    DebtorCreditorID (P.K Counter)

    DebtorID (F.K: Debtor, UQ)

    Creditor (F.K: Creditor, UQ)

    (There will be a trigger to check that one of creditor or debtor can have value)

     

     

    Check

    ------------------

    CheckNumber (P.K)

    Amount

    AccountNumber

    AccountName

    Date

    Source(F.K: DebtorCreditor)

     

     

     

    I wish it is clear now and you can understand what I mean :)

    if you want to solve this problem with my 3/3  solution then this will be the design:

     

     

    Person

    --------------------

    PersonID (P.K)

    FirstName

    LastName

    MiddleName

     

     

    Creditor

    --------------------

    CreditorID (P.K)

    PersonID(FK: Person)

     

     

    Debtor

    --------------------

    DebtorID (P.K)

    PersonID(FK: Person)

     

     

    Check

    ------------------

    CheckNumber (P.K)

    Amount

    AccountNumber

    AccountName

    Date

    CreditorSource(F.K: Creditor)

    DebtorSource(F.K: Debtor)

     

    then there will be a trigger to check that one of CreditorSource or DebtorSource can have value.

     


    Nothing is Impossible
    Thursday, August 11, 2011 2:55 AM
  • The solution i have been suggesting so far relied on Creditor and Debtor having one record per check. From the example provided, i see that this is not the case. The extra data in the Creditor and Debtor TABLEs are for the person, not the check.

    The issue here is there is one PersonId, which has two child ids, one for Creditor and one for Debtor. The Check is applied to either the CreditorId or the DebtorId based on the id. Being these ids are unique, this causes issues.

    I would suggest not to use unique ids for Creditor and Debtor. The record in Creditor for that Person means it is a Creditor record for that person and the same goes for Debtor. Thus, there is no reason to have a second unique id there. The Check TABLE can simple say which sub-account it is (Creditor or Debtor):

    Account_Type (Creditor, Debtor)
    ------------
    Name(PK)
    Description

    Person
    --------------------
    PersonID (P.K)
    FirstName
    LastName
    MiddleName

    Creditor
    --------------------
    PersonID(FK: Person)(PK)

    Debtor
    --------------------
    PersonID(FK: Person)(PK)

    Check
    ------------------
    PersonID(FK: Person)
    Account_Type (FK)
    CheckNumber (P.K)
    Amount
    AccountNumber
    AccountName
    Date

    I would also suggest not to use CheckNumber as the PK. CheckNumber usually refers to a number in the corner of the document. Due to bank written checks, there may be no number. I would use an autonumber for the PK, and use CheckNumber as an informational field.
    Thursday, August 11, 2011 11:37 AM
    Answerer
  • Again thank you very very much for your answer

    We cannot use this because in your design each person can only have one creditor and one debtor. While in my system each person can have more than one debtor. For every property he/she will buy, accounting department will give him/her new debtor account to be able to follow his payment. This is the culture of that company. That is the reason that I created a unique Code for creditor and debtor.

    Same things happening for creditor :)


    Nothing is Impossible
    Thursday, August 11, 2011 3:33 PM
  • I see.

    In that case, there should likely be two Check TABLEs. One for Creditor and one for Debtor. Two check TABLEs are nice because it clearly shows Check is a child.

    Your option 3 would also work, but instead of having a TRIGGER enforcing the rule, i would use a CONSTRAINT:
      CHECK((CreditorSource IS NULL AND DebtorSource IS NOT NULL) OR (CreditorSource IS NOT NULL AND DebtorSource IS NULL))

    This is nicer as there is only one Check TABLE, but it is slightly more confusing.

    • Marked as answer by Saman Saadat Friday, August 12, 2011 2:47 AM
    Thursday, August 11, 2011 3:43 PM
    Answerer