related categories - database design

Question

Hello!

I'm looking for a little database design advice...

I have a spreadsheet with a few columns in it. Column 1 being a list of categories and the rest being related categories(to the category in column 1). I'm trying to figure out what the best way to setup the tables would be... My thought so far is to have a table that just lists the categories then have a table with 2 columns that holds the id of the category and the id of a related category.... Would this be the best way to do this? Any better ideas?

Answer 1

A self referencing table (parentId to childId) is how most people implement a hierarchical structure like the one you are describing.

Answer 2

m:n relations should always (maybe there are exceptions) be made with extra table for relations. So it should be the most flexible solution

Answer 3

Your way is the best. Academics would call it resolving a reflexive many-to-many relationship with an associative entity, but it's nothing more than what you described.