Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Peformance Considerations for Bridge Entities

    [This post assumes SQL Server 2008]

    Is there any performance consideration for using a bridge entity vs. putting a Foreign Key in another "master" table? For example, suppose I have an Employee and their Department. I have a master table of Employees and a master table of Departments. Are there query performance considerations over putting a dept_id field in the Employees table over creating a bridge entity that contains an identity field, employee_id, and dept_id? I realize that there is another join for queries, but is it significant to performance?

    Code:
    Employees          vs.          Employee_Departments
    ----------                      ----------------------
    id                               id
    dept_id                           employee_id
                                      dept_id
    How about a one-to-many situation? Suppose I have a table acting as a forum post history. Every new or edited post adds a new record to the Posts table, effectively like a transaction table / audit trail. In this situation, would putting the thread_id in the Posts table, and using a Composite Primary Key of thread_id, post_id, date_posted - or alternatively - an identity Primary Key, have any performance impact in queries vs. a Posts table with just a post_id and a bridge entity containing an identity field, thread_id, post_id, and date_posted?

    Code:
    Posts                        vs.         Thread_Posts
    ----------                               ---------------
    id                                      id
    thread_id                                thread_id
    date_posted                            post_id
                                             date_posted (or some other indicatior for ORDER BY
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You either need the "bridge" entity or you don't. If you don't need it, i.e. if each employee can only ever be associated with one department, then the dept_id in employees is the correct solution, and probably slightly more efficient (though not a big deal probably). If you do need it, i.e. if an employee can ever be associated with more than one department, then it is the correct solution and so the question of efficiency doesn't arise.

    There is no point in having a surrogate ID in a "bridge" table by the way: the primary key for employee_departments should be (employee_id, dept_id). If you insist on having the surrogate (and you wouldn't be alone in that around here) then you also need a unique constraint on (employee_id, dept_id).

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst View Post
    If you insist on having the surrogate (and you wouldn't be alone in that around here) then you also need a unique constraint on (employee_id, dept_id).
    such a shame that the people advocating a surrogate key in ~all~ tables, regardless of whether it makes sense, appear to be otherwise quite sane

    i love how this trips up the newbs, who overlook creating the composite UNIQUE constraint, and then post urgent threads on database forums like "HALP!! how do i delete all but one of my duplicate rows???"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Yes, when I use a surrogate key, I'll always put a unique constraint on the pair of fields. I'm not clear of what either of you consider "good" regarding surrogate keys, but from an application developer's point of view, it's cleaner to use something like ...

    If myObject.ID = DataGridView1.CurrentRow.Cells(0).Value

    ... instead of ...

    If myObject.ThreadId = DataGridView1.CurrentRow.Cells(1).Value AndAlso _
    myObject.PostId = DataGridView1.CurrentRow.Cells(2).Value

    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't speak perl or whatever language that was

    but it looks like you are testing for the existence of a row

    which to me, no offence, i don't understand why you need to test both columns

    some queries, you use the bridge table to qualify the results you want, like an inner join where you know the bridge column is never not going to have a value

    other times, you don't care if the row exists or not (e.g. outer join)

    in all cases you write the appropriate SQL to do the logic for you

    so i don't understand why you think testing the surrogate key is better than testing one of the composite columns

    the thing is, you never have to test more than one of the two columns of a composite primary key (which is the way most people design a bridge table), since neither of them can be null, so a missing value indicates an unmatched row and this only happens in an outer join, if you want it to

    so i don't see what point you are making

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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