1

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?

v1n1akabozo
  • 257
  • 1
  • 4
  • 11
  • Are you saying that you want to end up with 3 tables? This can't enhance either performance, reliability or readability. – NoChance Jul 07 '15 at 21:51
  • You now have a 3 table join as opposed to a 2 table join when you want to join tables A & B. You can run tests on your Microsoft SQL Server version to be sure, but I suspect you won't be able to measure the performance difference. – Gilbert Le Blanc Jul 07 '15 at 21:51

4 Answers4

4

If this is a clear 1:n-relation (and ever 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.

HTH

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Yes, i considered this. One information i'd might want to keep is the exact time when the relationship was created, in this case i would need the 3rd table. It's not the case this time though. The readability is important IMO, despite the fact there's no technical need for another table, because it will be a big system and i'm designing it by my own, so if there's no performance issues i'd rather go through this path. Thanks for thw answer! – v1n1akabozo Jul 07 '15 at 22:01
  • Glad to help you! And yes, you are perfectly right: Adding information like the timestamp mentioned is a good reason for a joining table. – Shnugo Jul 07 '15 at 22:03
2

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.

C. Tewalt
  • 2,271
  • 2
  • 30
  • 49
  • Thank you for the answer. I knew there would be a performance hit as it's not the simpler approach, but i wanted to know whether it would be negligible or a big hit. As i commented to other fellow, as i'm designing it on my own and it will be mantained by many other programmers with mixed levels of skill i want it to be as readable as it can be without harming (too much) it's performance. – v1n1akabozo Jul 07 '15 at 22:04
  • @v1n1akabozo The type of keys will also help/hinder performance. e.g. auto integer keys with identity set in SQL server will be a faster join than by a string, or composite key – C. Tewalt Jul 07 '15 at 22:08
  • @v1n1akabozo If you're not talking about millions of records, then that certainly helps too – C. Tewalt Jul 07 '15 at 22:09
  • 1
    Oh yeah, i'm aware that joining/creating indexes with strings is not the best idea. One index of a single char would be ok as it's translated to it's ascii code, but i'm dealing with identity integer keys. – v1n1akabozo Jul 07 '15 at 22:16
  • @v1n1akabozo I figured as much since you're using SQL Server :) – C. Tewalt Jul 07 '15 at 22:19
2

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • I didn't think about the possibility to make it a n->n relationship when i first considered the second approach, well pointed. For this particular relationship it most likely, almost certainly, won't happen, but it can be the case for other ones that i might design in the future. Thanks for bringing this up! – v1n1akabozo Jul 07 '15 at 22:07
2

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.

Community
  • 1
  • 1
user1872637
  • 56
  • 4
  • Well, yes, looking through this point of view the only thing that can keep it readable is the convention i might use to name those tables. One other thing that i think that must be considered is that when using a join table i can assume a 0-to-many relationship without marking fields as empty. I'm trying to use as few null fields as i can. – v1n1akabozo Jul 07 '15 at 22:13