database-design - Is this a correct database design? (pk fk set and references)

Question

So parents can have many clubs and children. Each clubs can have many teams that have children in them. Children can join multiple teams.

I am confused with all these many relationships and I created a schema using a tool here: https://dbdiagram.io/d/5c455e3807ce540014df3eb2

Did I properly set keys and referenced them here?

标签： database-designforeign-keysrelational-databaseprimary-key

Answer 1

你的关系表设计看起来不错。作为替代方案，您可以考虑对父级和子级使用分层表。但是，这是设计考虑，完全取决于你（遵循 YAGNI（你不会弄坏它）原则）

Table person {
  id int PK
  name varchar
  parent_id int
}

Table club {
  id int PK
  person_id int
  class_id int
  name varchar
}

Table team {
  id int PK
  club_id int
  children_id int
  num_of_children int
  name varchar
}


Ref: person.id < club.person_id
Ref: club.id < team.club_id
Ref: team.children_id < person.id
Ref: person.parent_id > person.id