CODEWITHSUNDEEP
sql-serverdatabase-design
v1n1akabozo
v1n1akabozo

Reputation: 255

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?

Upvotes: -2

Views: 521

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

If this is a clear 1:n relation (and always will be!) there is no need (and no advantage) of a new table in between.

Such a joining table you would use to build a m:n relation.

There could be one single reason to use a joining table with a 1:n relation: If you want to manage additional data specifying details of this relation.

Upvotes: 4

user1872637
user1872637

Reputation: 56

Yes, at least you will need one more join to access TableB fields and this will impact the performance. (there is a question regarding this here When and why are database joins expensive?)

Also relations that uses a join table is known as many to many, in your case you have a PK on the middle table "making" this relation one to many, and this is less readable than the approach A.

Keep it simple as possible, the approach A is perfect for one to many relationships.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96600

If you index correctly, there should be very little performance impact although there will be a very slight extra cost that is likely not noticeable unless your database is maxed out already.

However, if you do this and want to maintain the idea that each id from column b must have one and only 1 a, then you need to make sure to put a unique index on the id for table b in the join table. Later if you need to change that to a many to many relationship, you can remove the index which is certainly easier than changing the database structure. Otherwise this design puts your data integrity at risk.

So the join table might be the structure I woudl recommend if I knew that eventually a many to many relationship was possible. Otherwise, I would probably stick with the simpler structure of the FK in table b.

Upvotes: 2

C. Tewalt
C. Tewalt

Reputation: 2509

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.

Upvotes: 2

Related Questions