sql-serverdatabase-design

To use a FK in a one-to-many relationship versus using a join table


First of all a little bit of context:

TableA as ta
TableB as tb

One 'ta' has many 'tb', but one 'tb' can only be owned by one 'ta'.

I'd often just ad a FK to 'tb' pointing to 'ta' and it's done. Now i'm willing to model it differently (to improve it's readability); i want to use a join table, be it named 'ta_tb' and set a PK to 'tb_id', to enforce the 'one-to-many' clause.

Are there any performance issues when using the approach b in spite of approach a?


Solution

  • Whenever you normalize your database, there is always a performance hit. If you do a join table (or sometimes referred to as a cross reference) the dbms will need to do work to join the right records.

    DBMS's these days do pretty well with creating indexes and reducing these performance hits. It just depends on your situation.

    Is it more important to have readability and normalization? Then use a join/xref table.

    Is this for a small application that you want to perform well? Just make Table B have a FK to its parent.