Hierarchical Data in MySQL

[Dragonkai]

I have a theoretical problem,

If I were to, and I am going to use the Nested Set Model for the hierarchical database. If I don't how many subsets there are going to be, and I wish to add new extra parent nodes and child nodes. After I've already set all the numbers. How would I be able to do it?

I see a few problems, like updating the set numbers and also I can't exactly just push a new child into the main hierarchy table. I can only update, which then requires recursive updating.

Any help appreciated.

[Dragonkai]

Sorry for double post, couldn't edit.

However, I'm in a dilemma. I need to make a hierarchical database for products. But I don't quite understand the the Nested Set Model. For example, at the last set, it says "tubes" or may be "shirts" but what is this? Is it a table name, so during a search goes through this and goes to the table. However if it is, I also have another set which also has "shirts" in it aswell. However these are adult sizes.

What do I do with two different tables named the same?

And if I do make a two different tables called different names. It ultimately makes the hierarchical database useless, since it is exactly like the flat version.

What I need to know is this.

I have information (product information) in hierarchical form.

Example:

Main Products
-> Featured
-> Clothes ->men ->shirts
|||||||||| ->kids ->shirts
||||||||||||||||| ->shoes
(as you can see both men and kids have "shirts")

The solution I'm trying to find is, a way to turn that into a actual working database.

Basically to avoid duplications. If I query mainproducts, it would give me everything. But it isn't a separate table. It simply joins all of it's childs. And so on..

Until I get to very last table. For example: shirts. And that is actual real metaphysical table. It's only real table (the very last children).

So can anyone please tell me if I'm on the right track with this model. And explain how I could implement such a database as a real mysql database, with or without referring to the nested sets.

So I don't understand what this main table means. Is it just a reference point. And if so, well the problem I stated up there ^^ (in this post) is quite disturbing.

For Your Information: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Thats where I got the idea from.

[chazzy]

The nested set model was thought up by someone over at mysql, because they couldn't figure out how to implement a hierarchical query in the DB (I'm kidding of course).

I think in this scenario, it makes sense to first post the problem - why exactly is it that you think you need to use the NSM? Then we can help you figure out how to implement it. It could end up you don't need to use it. To me, I don't see how clothing fits into the parent/child relationship.

Based on your description, I think it makes more sense for there to be a many to many relationship between products and categories. You could have categories defined as Clothes, Mens, Kids, Shirts (for examples, that's 4 categories right there). Then a single actual shirt might fall into Clothes, Mens, Shirts, or Clothes, Womens, Handbags.

[Dragonkai]

Many to many relationships?

How would it work, as in if I needed to say search shirts for mens. How would the query work, does it query need to query mens and shirts and they have to match or is there a column in the shirts table that says its in Mens.

Also in this many to many relationships, wouldn't there be a big duplication in data. Like the data in clothes would be duplicated in the data in Mens. And Men's stuff would be everything. How would I keep track everything unless I have identifier columns. Or using foreign keys in almost everyone of the tables to relate to another table.

This is complicated because we have alot of different products. And hierarchical approach seemed to make sense.

Or is there something I'm missing in the capability of Mysql.

[Dragonkai]

Ok I think I understand.

After reading: http://www.tonymarston.net/php-mysql/many-to-many.html

I realized that basically:

Say for example, I had the table for pants and a table for shirts.

And in this table there were mens pants and mens shirts and kids pants and kids shirts.

Now in this table there is a column which identifies that it is a men or womens or kids.

Now in table 3,

Would be simply called kids clothing.

Now as it said in the tutorial, I think what you do is put two columns which identifies pants table and shirts table, respectively.

But rather than just any id. This id, will be the one that identifies shirts and pants that are for kids. And this id will be also in the pants and shirts tables of course.

Am I on the right track, or is there something I'm missing?

[chazzy]

fundamentally, i think you're starting from a bad spot. there shouldn't be a table for shirts and a table for pants. there should be a table for products, and the type of product (shirt, pants) represents one of the categories that the product is in.

[Dragonkai]

Ah.. so you're saying,

I should have one single massive table with one column being the name of the products (barcode) and have others which are like shirts and pants. And say for a shirt, it could be #215588 and 1 in the column for shirts and NULL for pants.

And the 1 means that this is a shirt.