[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?

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?

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).

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???"

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

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