0

The mysql database has a table "positions". Every position is either a "parent", "child" or none of both. If it is a parent, then this position has a connection to 1 to n "child" positions. If a position is a "child", it has a connection to exactly one "parent" position. If the position is none of both, it is just on its own. Should i split this table in three tables and create a relation of parent/child? Later i will want to filter a query and every time a position is kept AND a parent (or a child), then i want to keep all its children (or the parent). Basically i want to keep the whole group of parent/children if only one of all of these is kept (kept after filtering e.g. if the position_id is prime or what ever) What is the "way to go"?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Timo
  • 23
  • 5
  • The answer is a matter of opinion, and it's hard to form an opinion without knowing more about those `positions` entities and how you will use them (SELECT, INSERT, and UPDATE them). Efficiency first? Maintainable code first? Abstract elegance first? The answer to your question depends on those factors and many others. – O. Jones Jun 18 '22 at 23:01
  • The best method also depends on which version of MySQL or MariaDB you use, if it supports recursive queries. Note that MariaDB and MySQL are different products, and should not be considered compatible or flavors of the same database. – Bill Karwin Jun 18 '22 at 23:15

0 Answers0