SQL Server Performance

Self Referencing Foreign key

Discussion in 'SQL Server 2005 General DBA Questions' started by shankbond, Mar 22, 2010.

  1. shankbond New Member

    Hi,
    Is there any performance benefit from a self referencing foreign key, If I have a choice for reference from other table or the current table; what should I use?
  2. FrankKalis Moderator

    This is not really a performance related question, but rather one that touches your data model. If your model requires such a self-reference, so be it. What are you trying to model?
  3. shankbond New Member

    Hi Frank,
    actually I am trying to create a database to manage teams,
    like there is a table employees for a company
    and another table which has the team of the particular employee,
    for example team lead, manager, group manager etc.
    This table also has a column employeeid which is already referencing the previous table,
    my question was whether I should reference this column as a self referencing foreign key, for other columns: team lead, manager, group manager etc.
    since all the employees will be having an entry in this table as well.
  4. FrankKalis Moderator

    If I understand you correctly, you need this EmployeeTeam table, since I suspect one employee can be member of multiple teams or one employee could act in different roles for different teams.
    If so, this also means that you need the FK reference to the Employee table. It can't be self-referencing, because from your description I don't see a proper PK in the EmployeeTeam table that is unique per employee. It can't also be self-referencing, because then you could never be sure that you're always dealing with an employee that does exists ( = has an entry in the Employee table).
  5. shankbond New Member

    [quote user="FrankKalis"]If I understand you correctly, you need this EmployeeTeam table, since I suspect one employee can be member of multiple teams or one employee could act in different roles for different teams. [/quote]
    No, because I am just breaking a big table into logical groups, also the employee in question can be shifted to different team, but won't be a member of multiple teams.
    [quote user="FrankKalis"]It can't be self-referencing, because from your description I don't see a proper PK in the EmployeeTeam table that is unique per employee.[/quote]
    Maybe I forgot to tell You that there is a unique Id (employee id) for every employee in the company.
    also can You or someone else please explain how to relate a table?
    lets say for other example for a big table like candidateinformation
    firstname,
    middlename,
    lastname,
    phone,
    candidateid( surrogate key; identity)
    Then in other table candidateproffesionaltable
    recent employer,( nullable)
    experience,( nullable)
    current employer,
    other columns.....
    how should I link these two tables( create relationship) should I create a candidateid column in the second table or generate another surrogate key for this table and vice versa.
    The examples I have seen so far on the internet they all generate a new surrogate key for second table and will always reference that in first table.( I don't know the reason why?)--It would be nice if someone explains it :)
    Last point: There are scenarios when a parent table may or not have a child record( which at most can be one).. Do you people use Junction Tables or Cross Referenced tables in this scenario.
    Any help will be greatly appreciated, this will also help other newbies like me in future as well.
  6. satya Moderator

    If that key is a GUID (UniqueIdentifier) then its a big problem in performance, as I have seen.
    Before deciding on whether to use declarative constraints or triggers to support referential integrity, careful examination of the integrity requirements of each referential constraint should be performed. For certain types of constraints, declarative RI will not be an option. Remember that declarative RI can be used to support only the RESTRICT rule.
    For better performance you can code/refer that RI in the SPs for optimization.
  7. shankbond New Member

    [quote user="satya"]If that key is a GUID (UniqueIdentifier) then its a big problem in performance, as I have seen.[/quote]
    It is a Identity Column
    [quote user="satya"]For better performance you can code/refer that RI in the SPs for optimization.[/quote]
    I will be using Stored Procs to Insert the values in the tables(parent- child) rather than using triggers.
  8. FrankKalis Moderator

    Okay, hopefully I understand you now correctly. Bear with me, it's quite early in my place and I haven't had my morning wake-up coffee yet. [:)]
    If any employee can be only member of one team at any given time and can have only one role in that team, you only need the Employee table with a Team key and a Role key. These would then reference some lookup tables. I see no real need for having a separated table for managing teams and roles that would reference the Employee table.
    It might be different when there is really a lot of activity on the Employee table with people joining & leaving company or moving teams frequently, etc... In such a case it might be beneficial to offload this to a separated table. But this would mean really a LOT of activity, which usually isn't the case for employees.
  9. shankbond New Member

    <p>[quote user="FrankKalis"]Bear with me, it's quite early in my place and I haven't had my morning wake-up coffee yet. <img src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif" alt="Smile">[/quote]&nbsp;</p><p>Thank You, very much&nbsp; for the reply. Looks like You are working too much or are SQL alcoholic <img src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif" alt="Smile">.</p><p>[quote user="FrankKalis"]If any employee can be only member of one team at any given time and can have only one role in that team, you only need the Employee table with a Team key and a Role key. These would then reference some lookup tables. I see no real need for having a separated table for managing teams and roles that would reference the Employee table. [/quote]&nbsp;</p><p>It means that there will be two tables now one having roles ID, and other one having team members of a particular team.</p><p>&nbsp;</p><p>Do You know some good article or blogs regarding such basic data warehousing ? </p><p>How You people judge that there should be a different table for a particular logical entity or so..? <br></p>

Share This Page