locked
Implications of Many FK relationships to a single table RRS feed

  • Question

  • What are the best practices in relation to creating multiple FK relationships to a single table (I have a Users table and a 'LastModifiedBy' field in many tables) are there any performance/design issues I should be aware of in this case?

    mcr1322


    MC
    Monday, January 24, 2011 6:04 PM

Answers

  • Physical FK constraints have own pros and cons. The biggest pros are:

    1. Data is consitent

    2. It helps optimizer in some cases (especially with the select against views when SQL can illuminate joins)

     

    The cons are that for every insert operation into detail (referencing) tables, SQL needs to perform index seek to make sure master (referenced) table has the corresponding row. And for every detail from the master (referenced) table, SQL Server needs to check all detail (referencing) tables. So rule #1 - make sure you have the indexes on the referencing columns in details table.

    Although, those cons (assuming you have indexes) are typically not an issue unless you have the system working under heavy IO load (hundreds/thousands of data modification ops per second).

    You can check: http://aboutsqlserver.com/2011/01/20/referential-integrity-part-1-foreign-keys/

     


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NEditor Wednesday, February 2, 2011 5:30 PM
    • Marked as answer by WeiLin Qiao Wednesday, February 9, 2011 4:47 AM
    Wednesday, February 2, 2011 5:14 PM

All replies

  • I don't think there should be any problem in linking many tables to Users table.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, January 24, 2011 6:31 PM
    Answerer
  • Physical FK constraints have own pros and cons. The biggest pros are:

    1. Data is consitent

    2. It helps optimizer in some cases (especially with the select against views when SQL can illuminate joins)

     

    The cons are that for every insert operation into detail (referencing) tables, SQL needs to perform index seek to make sure master (referenced) table has the corresponding row. And for every detail from the master (referenced) table, SQL Server needs to check all detail (referencing) tables. So rule #1 - make sure you have the indexes on the referencing columns in details table.

    Although, those cons (assuming you have indexes) are typically not an issue unless you have the system working under heavy IO load (hundreds/thousands of data modification ops per second).

    You can check: http://aboutsqlserver.com/2011/01/20/referential-integrity-part-1-foreign-keys/

     


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NEditor Wednesday, February 2, 2011 5:30 PM
    • Marked as answer by WeiLin Qiao Wednesday, February 9, 2011 4:47 AM
    Wednesday, February 2, 2011 5:14 PM
  • "Last modified by" is a typical case where you should not use a FK Relation. You simply put the Username (DBUser, Original Login, Windows User, whatever) as a character value with no reference/relation to a User table. 
    If a user leaves the company, his login will be removed from the ADS (after a while maybe 1-3 month) so you would lose the information. The other way as a string in the LastModifiedBy the information is always there.

    And to be honest, the lastModifiedBy is important in the first 12 month after a datamanipulation, after a certain period it isnt anymore important who modified the data.

    Regards Paolo

    Thursday, February 3, 2011 9:17 AM