Results 1 to 2 of 2

Thread: Design Question

  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unanswered: Design Question

    Hi- I have a situation where the FK to a table could come from three different tables. As an example I have TableA, TableB and TableC. TableD could be a child table with Ids from any of the first three tables. For any row, it will have the Id from only one of the above three tables. So I have two design options. First,
    Table D:

    TableDId,
    Col1,
    Col2,
    TableAId,
    TableBId,
    TableCId
    etc...

    So for each record, one of three Ids would be not null.

    Alternate design is
    TableDId,
    Col1,
    Col2,
    TableABCId (This could be an Id from any of the A, B, C Tables)
    TableName (To specify if the above Id is from TableA, TableB or TableC).

    Which of the above two designs do you recommend. And if there is a third, better option, what would that be?

    Thanks a lot for your time.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The first design is MUCH better. You can declare foreign key constraints to enforce the integrity between table D and tables A, B, and C. If you really need it, you can create a CHECK constraint to insure that only one of the three FK values is NOT NULL, but I wouldn't do that because it has always bitten me in the past (there is always an exception, and sometimes the baseline rules change to allow more than one non-null value).

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •