-1

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: let's 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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ein Google-Nutzer
  • 1
  • 2
  • Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Aug 14 '23 at 02:36

1 Answers1

0

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
Gaël J
  • 11,274
  • 4
  • 17
  • 32
  • Thanks! I guess that we can simply take as granted that any database is very good at this basic task of looking up a certain index in a table. So trying to "help" by adding the person keys again in the users table would just make everything less maintainable without any benefit! – Ein Google-Nutzer Aug 13 '23 at 07:29
  • 1
    @EinGoogle-Nutzer And also worth mentioning the very likely problem of deadlocks when adding and removing and reading happen at the same time. I'd suggest you take the time to read up on [ACID](https://en.wikipedia.org/wiki/ACID). – Filburt Aug 13 '23 at 07:41