title: Best schema for a relational database with 2 parents and childs in mysql
body: <p>I have 2 ways to solve a relational problem, but I don't know which one is better or even if they're correct/would work.</p>
  <p>I have 3 tables, <code>category, subcategory and skill</code>. Category is the parent of subcategory, and subcategory is the parent of skill.
  Each category can have many subcategories, but a subcategory can belong only to one category. So it's many to one.
  Each subcategory can have many skills, and each skill can belong to many subcategories. So it's many to many.</p>
  <p>Option 1: Create a foreign key in each child table:</p>
  <pre><code>CREATE TABLE Skills_Category (
      categoryId int(4) AUTO_INCREMENT NOT NULL, 
      name varchar(150) NOT NULL, 
      PRIMARY KEY (categoryId)
  );
  CREATE TABLE Skills_Subcategory (
      subcategoryId int(4) AUTO_INCREMENT NOT NULL,
      name varchar(150) NOT NULL,
      categoryId int(4) NOT NULL,
      PRIMARY KEY (subcategoryId),
      CONSTRAINT Constr_Skills_Subcategory_Subcategory_fk FOREIGN KEY Subcategory_fk (categoryId) REFERENCES Skills_Category(categoryId)
  );
  CREATE TABLE Skills_Skill (
      skillId int(4) AUTO_INCREMENT NOT NULL,
      name varchar(150) NOT NULL,
      subcategoryId int(4) NOT NULL,
      PRIMARY KEY (skillId),
      CONSTRAINT Constr_Skills_Skill_Skill_fk FOREIGN KEY Skill_fk (subcategoryId) REFERENCES Skills_Subcategory(subcategoryId)
  );
  </code></pre>
  <p>Option 2: Create a relational table between parent and child:</p>
  <pre><code>CREATE TABLE Skills_rel_Category_Subcategory (
      Category INT NOT NULL, 
      Subcategory INT NOT NULL, 
      PRIMARY KEY (Category, Subcategory), 
      CONSTRAINT Constr_Skills_rel_Category_Subcategory_Category_fk FOREIGN KEY Category_fk (Category) REFERENCES Skills_Category(categoryId) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT Constr_Skills_rel_Category_Subcategory_Subcategory_fk FOREIGN KEY Subcategory_fk (Subcategory) REFERENCES Skills_Subcategory(subcategoryId) ON DELETE CASCADE ON UPDATE CASCADE
  );
  CREATE TABLE Skills_rel_Subcategory_Skill (
      Subcategory INT NOT NULL, 
      Skill INT NOT NULL, 
      PRIMARY KEY (Subcategory, Skill), 
      CONSTRAINT Constr_Skills_rel_Subcategory_Skill_Subcategory_fk FOREIGN KEY Subcategory_fk (Subcategory) REFERENCES Skills_Subcategory(subcategoryId) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT Constr_Skills_rel_Subcategory_Skill_Skill_fk FOREIGN KEY Skill_fk (Skill) REFERENCES Skills_Skill(skillId) ON DELETE CASCADE ON UPDATE CASCADE
  );
  </code></pre>
  <p>Will both answers solve my problem? If yes, which one is better? More information about the final idea can be found <a href="https://stackoverflow.com/questions/73479881/show-many-to-many-relationship-table-in-nodejs-with-mysql">here</a></p>
  <p>&quot;edit your question to highlight the difference between the associated question and yours&quot;:
  As you can see in my <a href="https://stackoverflow.com/questions/73479881/show-many-to-many-relationship-table-in-nodejs-with-mysql">other post</a> that I linked I have already see that answer to create the second option. But my problem persist, since I don't only have a many to many relation but also a many to one.</p>
  <p>So for the many to many relation could be better the second option but the many to one could be either the first or the second option, or maybe they won't work, because that's what I'm asking.</p>