Design mySQL schema to save ebay categories

Question

In my app, I am making a call to ebay API and save their categories to my local table.

The API response is a list of categories.

1. Each category could have more than one parent.
2. Each parent category could have more than one child category.
3. There could be up to 6 sub categories

How should the table structure look like? I am using MySQL and Hibernate.

The tree approach of id, name, parent_id cannot work as id could have more than one parent_id

Answer 1

you could use another table where you store only the parent-child relationships

category          children
-----------       ------------
id INT            category_id INT
name VARCHAR      child_id INT
...

example

as a tree:

                       cat1
                     /   |  \
                cat2 – cat3   cat4
                         | \
                       cat5  cat6

in words:

cat1 has children: cat2, cat3, cat4
cat2 has no children
cat3 has children: cat2, cat5, cat6
cat4 has no children
cat5 has no children
cat6 has no children

In your tables it would looke like this:

category          children
------------      ----------
1, "cat1"         1, 2
2, "cat2"         1, 3
3, "cat3"         1, 4
4, "cat4"         3, 5
5, "cat5"         3, 6
6, "cat6"         3, 2

So in your additional table children you don't have a unique id but you can have each category_id multiple times, each time relating it to another child

[EDIT]
I added 2 to be the child of 3 and 1 (not that obvious in the tree). The children ids and the parent ids can occur multiple times in the children table. They are not unique.

[EDIT2]

This is a SQL query to get one parent and all its children (first generation):

SELECT id, name 
  FROM category 
  WHERE id=1 
UNION 
 (
  SELECT children.child_id, category.name   
     FROM children 
  JOIN category 
     ON children.child_id=category.id 
  WHERE children.category_id=1
 )

Haven't tested it, but something like this should do the trick.
It will return this list:

1 "cat1" 2 "cat2" 3 "cat3" 4 "cat4"

which you can format like this:

 1 "cat1"      <-- parent   
 |          
 +– 2 "cat2"     <-- the children
 +– 3 "cat3"
 +– 4 "cat4" 

Now you will have to play with JOIN and UNION a little bit or simply call this statement in a nested loop for every parent and every child generation.

MySQL is holding 2D- tables with rows and columns, so to have more dimensions (like more generations) you have to put the data together either with the JOIN / UNION statements or outside the DB in your program.

Answer 2

Take a look attached image for relations, You needs to extend according to your need. Hope it will help you to design your schema.