-1

I am creating a React/Laravel web application that will allow the user to search and navigate through celebrities based on their 'categories'. I have set up a Categories table using MySQL like so:

*CATEGORIES_TABLE*
id      name                   parent_category_ids     children_category_ids
1       Music                  0                       23,24,25,26,27,28,29,30,31...
2       Sport                  0                       45,46,47,48,49,50,51...

... continued ...

23      Pop Music               1                       120,121..

... continued ...

45      Soccer                  2                       180,181...

... continued ...

180     Club Teams              45,2...                  250,251,252...
181     International Teams     45,2...                  301,302,303...

... continued ...

250     Real Madrid FC          180,45,2                 1045,1046,1047,1048,1049,1050...
251     Barcelona FC            180,45,2                 1080,1082,1083,1084,1085,1086..




*CELEBRITIES_TABLE*
id      name                   category_ids
1       Justin Bieber          1,17,18...
2       Christiano Ronaldo     2,24,32,34...

As you can see, categories can have multiple parents and multiple children, and celebrities can have multiple categories.

My issue is that I'm not that experienced in Database Design so I'm not sure if this will work. I am worried that this setup will be hard to maintain and it won't be efficient at inserting/deleting new categories and celebrities etc.

Any thoughts on this would be much appreciated, I don't want to get stuck into it for it to not work, thank you!

4
  • 3
    Do not use CSV fields. Create M:N references using intermediate link tables.
    – Akina
    Apr 16, 2020 at 9:39
  • Thanks for your reply @Akina - I am looking into this now :) Apr 16, 2020 at 10:03
  • 1
    The problem with this approach is not with "efficient at inserting/deleting"; it's with querying.
    – mustaccio
    Apr 16, 2020 at 12:02
  • "Any thoughts " is not a valid question here. Follow an information modelling & design textbook & ask 1 specific researched non-duplicate question re where you are 1st stuck. Don't ask us to write another one.
    – philipxy
    May 3, 2020 at 2:39

1 Answer 1

0

For your examples every childs' parent_category_ids is just the parent's parent_category_ids with the parent's id added. These data form a tree structure. Trees can be modeled in several ways. The one you show is called path enumeration. There are others whuch I talk about here.

Not the answer you're looking for? Browse other questions tagged or ask your own question.