SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation mutually exclusive foreign keys

    What is considered best practice in the following case?
    To tables a, b and c there can be discussion threads associated, but a thread can be associated to only one table (a, b, or c)

    I've two ideas:
    1. (left layer): for each table a fk is defined. When one fk is not null, the others should be null.
    2. (right layer): one fk can point to either a,b or c. The associated table is stored in type. The fk-datatype must be compatible with the pk datatypes of the three tables.



    (this question should be in the forum faq i suppose :P)

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hms, I see someone has an opinion about this issue. To quote:

    Reason #4: Mutually exclusive relationships
    Too often I’ve seen databases with a foreign key that relates to one of five tables based on the value of a char(1) field. The space conserving mindset that comes up with this implementation is admirable, but it produces far too many negative side effects.
    What happens when the char(1) field gets out of sync with the foreign key field? What happens when someone deletes the foreign record or changes its primary key? More orphaned data happens.
    The solution is to use five fields that each refer to a single table. You may have more nullable fields that take up more space in the database, but it’s worth it in the long run.


Bookmarks

Posting Permissions

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