I just recently started using a SQL database for my new project and I have a question regarding One to Many relationships. A simplified example:
Lets say we have a User that can define a list of Persons (e.g. let’s think of a Sims game). Each user just creates their own persons, a person can not be shared between users.
So we have a list of Users, each user has a list of Persons. I would now represent this in SQL like this (sorry if I don’t use any official SQL representing syntax…):
Table Users
id: uuid (Primary Key)
Table Persons:
owner: uuid (Foreign Key from Users)
number: integer (Auto increment, starting from 0)
Primary Key: Composite of owner and number
name: text
age: integer
gender: text
etc
So now if I want to get a list of all Persons that belong to a user, I can simply query the Persons table to get all elements where the owner corresponds to the user. Ok.
Now my question: Isn’t that totally inefficient? I mean, if we have 1 Million Users that created 10 Million Persons, the SQL database has to query 10 Million Persons to find the maybe 5 persons that belong to the current user?
Wouldn’t it be more efficient to add a list of persons to the Users table, such that the "lookup" can happen faster? E.g. should I update the Users table to this?
Table Users
id: uuid (Primary Key)
persons: list of uuid (Foreign Key from Persons)
Table Persons:
id: uuid (Primary Key)
etc
I have seen in all recommendations that the first approach is better and more "SQL" like, but I guess the second approach must be orders of magnitude faster?
Thank you guys!
Mandatory part for StackOverflow:
What did I tried to do? Just a theoretical example
>Solution :
The owner column in your Person table will be a foreign key, which in addition to ensuring the relationship will also have an index.
Meaning it’s super fast and efficient to query the Person table filtering on owner = ....
Regarding the use of a list of persons in the User table, few things:
- list is not a native SQL type
- when it exists, this won’t allow foreign key and relationship
- it doesn’t scale well: the list will grow with the user, making one user a very large record in terms of storage