1

What's best practice for querying multiple tables and foreign keys?

For instance:

User has many Tasks has many Reminders

I would assume best practice would be to keep foreign keys to their direct relatives, ie:

tasks table: id, user_id

reminders table: id, task_id

...to avoid situations where you have a bunch of foreign keys connecting tables to all possible relatives.

Let's say I want to retrieve all of a user's reminders.

SELECT reminders.* FROM users
LEFT JOIN tasks ON users.id = tasks.user_id
LEFT JOIN reminders ON tasks.id = reminders.task_id
WHERE tasks.user_id = {#YourUserIdVariable}

Is there a point at which doing joins to retrieve a record outweighs the integrity of the db? (assuming that storing reminders.user_id would be a bad practive in the first place?

5 tables? 10?

Squadrons
  • 2,467
  • 5
  • 25
  • 36
  • 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:05

2 Answers2

2

The standard is not to store unecessarily redundant information, including redundant Foreign Key information that could be derived through relations/joins.

In practice this means, storing only the direct relationships' FKeys, and not secondary or implicit FKey info. For performance reasons you may at some point decide to try "short-circuiting" a relation, but technically that's a form of de-normalization. You can do it, but you only should do it when the need is apparent, in other words, proper normalization should always be the default, anything else needs to significantly justify itself.

As for "Is there a point at which doing joins to retrieve a record outweighs the integrity of the db?": Possibly, but this can only be determined on a case-by-case basis. It's not possible to reduce a business trade-off like this to a data-only technical rule.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
1

How much data do you expect to store in your database? If there are relatively small amount of rows in each table for the joins the execution time between a normalized and non-normalized database will be negligible.

So instead just go with a design that makes sense to you and you can normalize and de-normalize tables where it makes sense.

Here's a good read on the subject:http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html

fromanator
  • 854
  • 4
  • 9