Search code examples
mysqldatabaseheidisql

Category and Subcategories MySQL

i have 3 tables:

CREATE TABLE student
(ID INT PRIMARYKEY NOT NULL)

CREATE TABLE classA
(ID INT PRIMARYKEY NOT NULL)

CREATE TABLE classB
(ID INT PRIMARYKEY NOT NULL)

my main category is "student". my subcategories are students from either "classA" or "classB".

now my question is how do I implement this model in mysql? I've thought about creating an one to many relationship between student and classA and one between student and classB. Not sure if that's the best way and really realizes what i want.

i'm an major beginner and my english is pretty bad, so sorry in advance

Solution

  • It's all about your specification :)

    If a student should have many corespondents in a class table then yes you have to make one to many relationship.

    A primary key in student and foreign key on the ClassA and ClassB.

    Let's take a super simple example:

    If a student from student table will borrow some books (an you will keep those books in ClassA table) than this is one to many relationship between student table which have one row with the student id (primary key) and classA which will have many rows with same student id (foreign key here) and the books that he borrowed :)

    Hope it helps!