0

Say I have a table called 'child' that contains a foreign key referencing another table called 'parent'. If the parent table contains column values I frequently want to access when SELECTing from the child table, is it better to JOIN the tables on the foreign key or store the columns i'm frequently accessing from the parent table a second time in the child table.

Sometimes I also have a third 'grandchild' table that references the child table and want a mixture of information from all 3 tables. A triple JOIN seems like i'm over complicating it.

I feel like there's a much better way to go about this. If anyone has advice or some good resources on this topic, let me know.

user2923125
  • 77
  • 1
  • 7
  • 3
    The right answer is to avoid redundancy and store the data in only one place, unless you have a really good reason not too. Your reason doesn't sound like a really good one. – Gordon Linoff Jan 19 '14 at 20:23
  • Does this answer your question? [Is it better to store redundant information or join tables when necessary in MySQL?](https://stackoverflow.com/questions/3237033/is-it-better-to-store-redundant-information-or-join-tables-when-necessary-in-mys) – philipxy Apr 14 '22 at 22:06

1 Answers1

1

This question is based on premature optimization, which is bad.

You're talking about denornalization, which should only be done is there's a genuine and pressing performance problem. While your idea sounds enticing, it's almost always a bad idea, because:

  • you're only doing it for performance reasons, but databases are pretty fast - you're unlikely to benefit much anyway
  • denormalizing introduces complexity - if you change a child value you must keep the value in the parent updated. This is a big hassle (not going into detail here)
  • you don't even know if you have a performance problem: if it ain't busted, don't fix it
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • How would you recommend this be approached instead? – user2923125 Jan 19 '14 at 20:58
  • Store the data that belongs in the parent table in the parent table; store the data that belongs in the child table in the child table; store the data that belongs in the grandchild table in the grandchild table. When you need to access the information, join the tables. You should only need two joins to connect the three (parent, child, grandchild) tables in the ordinary course of events. – Jonathan Leffler Jan 19 '14 at 21:12
  • You could create a view that bundles the joins up so they look like a normal table to the application (for select queries anyway) – Bohemian Jan 19 '14 at 23:11