0

In my case i have 4 tables

  • Table tournaments has many trees
  • Table trees has many rounds
  • Table rounds has many matches

so that means if i wanna get all the matches of a specific tournament it would have to pass through all the 4 tables, so is it better to just add column tournament_id in all the matches, which maybe considered as redundancy ?

Hazem Emad
  • 419
  • 1
  • 3
  • 8
  • 3
    Ideally, you'd keep tournament_id where it belongs and make the joins; and if you're not using values from intermediate tables, a decent query optimizer should be able to zip through the PK indexes without even hitting the tables. But in real life this is not always the case; my opinion is keep to the ideal until it actually proves to be a problem. – Uueerdo Aug 03 '17 at 16:49
  • 2
    It depends on the scenario, but best practice is to design your database with normalization in the way that you currently have it. That is to say, don't repeat data (tournament_id is already in the trees table), and rely on indexes (in your case, pkeys/fkeys) to allow you to join your tables together to get to the data you need. You can use MySQL's `explain` to view the query plan, and see how much impact these multiple tables are having on your query performance - as well as to make sure the optimizer is choosing the correct indexes. – RToyo Aug 03 '17 at 16:52
  • Possible duplicate of [Can I use a counter in a database Many-to-Many field to reduce lookups?](https://stackoverflow.com/questions/45407402/can-i-use-a-counter-in-a-database-many-to-many-field-to-reduce-lookups) – philipxy Aug 04 '17 at 11:57

0 Answers0