hello all

I have a design problem at hand while designing a database in mysql. I have to store hierarchical data with following structure in database...

Items are divided into - vehicles and parts. Then vehicles are divided into categories like this -

item
-> vehicles
---> transport
------> car
------> bus
------> truck
---> agriculture
------> tractor
------> sowers
---> construction
------> cranes
------> bulldozers
------> escalators
-> spare parts

Every category listed above has a table to itself and stores the attributes for that particular category.

I read about the nested set and adjacency lists in the article here..
MySQL :: Managing Hierarchical Data in MySQL

How should I store my hierarchical data?

Should I use nested sets -
-> I am not sure if this technique can be applied, since there are going to thousands of items, there is no one root node.

Adjacency lists is the only remaining choice.

Can somebody guide me on this?

Thanks and Regards
Vikram

Why aren't these items all be in the hierarchy table to?

We had a good discussion on different ways to store hieracical data here.

Why would this be an issue?

What's wrong with using item or vehicles as the root node?

Shouldn't the spare parts for a type of vehicle come under the vehicle itself? I honestly have no idea but it just doesn't sound right.

Hello Mike
Thanks for guiding to some previous threads. They were illuminating.

A small clipping of my database should help in giving a clearer view of my problem. I am attaching it as an attachment.

The reason I can't have all the categories stored in the hierarchy table becoz they have different attributes.

In this case I cannot have nested sets as there would be item1, item2, item3 in the parent table. For every item there would be just one path down to the leaf.
Like an item can be one of vehicle or spare part. A vehicle can be either a transport vehicle or agriculture purpose one or construction purpose one. and so on...

What I am implementing here is an adjacency list. Can I use a nested set here? Am I missing something?

Our company has spare parts for some vehicles which we might not be selling. That is why I have kept it separate.

Thanks and Regards
Vikram

May be I am wrong. This is not a case of how to implement hierarchy of data in mysql. The problem can be better stated as inheritance of data in mysql.
Since, every car inherits some properties of a vehicle and very vehicle inherits some properties of an item.

Your diagram is very small for my poor eyes! I don't know very much about your application but I think you're making your solution too complicated. Here's a few small ideas:

• One table for all Vehicles holding data from Item, Vehicle and the common data from Car, Bus, Truck, Tractor, Seeder etc. You'll need a type field to identify the type of each vehicle ie this record is a car. Another type field could indicate whether the vehicle is for Transport or Agriculture.
  
• The Transport and Agriculture tables would contain the id and type of vehicle. There would only be records in the Transport table for those vehicles that are transport vehicles. This data could be held as nullable fields in the Vehicle table but it looks ugly so I didn't.
  
• The Parts table could still be kept separate. Odd that this table doesn't have a model field or a price field though.

I'd of given full table definitions but it would of meant having to type all the data in rather than just copy and pasting the fields. If you supply some text with the fields then I'd be happy to give my ideas of where all the fields go. That doesn't mean I'm right though!

Mike

PS I always used to have problems spelling vehicle but I've typed it so many times above that I think I've fixed the problem now.

"CTRL" + "+", in FF

pootle flump ur codings are working excelent.

Thanks for that! I've only just had my eyes tested (20/20!) but it still required a ctrl +++ to read that diagram. Maybe I need a bigger TV. It's a shame there isn't a copy function that will extract the text from a diagram.

Hello Mike...thanks for helping me out.

The type field is going to very cumbersome. I mean I will have to combine coding and database to check for a record. Like first I will have to check the type from a vehicle, if it is a car, then I will join the vehicle and car tables.

Oh, I did not send the complete database spec since it was too big to be convenient.

Sorry about that.

Thanks and Regards
Vikram

Looking at the fields in your diagram then you only need one table to hold the data for the Car, Bus, Truck, Tractor, Seeder fields but you'd just need a type field to say what type of vehicle each record is. The data in your Item and Vehicle tables are also shared by each type of vehicle so this data should also go into this same table.

Don't worry - we don't mind muddling around guessing our way to the end. Some of the longer term people on the forum are even rumoured to be psychic!

Hmmm....yes this makes sense.

however there is this type field for every car, bus, truck (apart from the vehicle type u have suggested whose values are car, truck, bus etc.).
Like for a car, car types are sedan, wagon and such. for truck there are pickup, dump and all. These will cause a problem. The types for each vehicle have to be stored in a separate table as well as there are going to be more car types, more truck types in future.

Also there are makers and models for each car, bus, truck which would be a foreign key to another table storing the car makers, car models, truck makers, truck models.
Had it not been for such fields I too wud have gone for the simpler design.

Cheers

You can have more than one type field in a table each with their own lookup table. It's better to use a type field than to create a new type of table each time a new type comes along. I assume this new fields (car_type) is a field you were going to tell us about later?

Had you told us about these fields then we could of included them in the design. I guess I should wait for a complete list list of fields, preferably in your current table design, before continuing further.

Hello Mike

actually these fields were there in the diagram I had attached earlier, but like u said the diagram wasn't legible enough, also I had not explained those fields.

Well, I took your advice and reduced some of the tables by making use of type of fields. Here is how it looks now. I am attaching a PDF, that should be much more legible than the image I attached earlier.

thanks and regards
Vikram

Perhaps my difficultly is trying to understand your unique style of diagram - many people try to use ER diagrams which work well. In fact even plain text with the fields listed under the table names works great. Your latest diagram contains new entities (ie motorcycles) and 50% of the fields have changed - I just feel like we're trying to herb cats at the moment.

herbing cats?

...far be it for me to pull up someoneelse's typos....

is that anything like herding cats

or is this some new form of feline abuse and spraying them with sage and onion, oregano....

Let's keep this topic on track people - I think Mike is making excellent progress.

pootle flump ur codings are working excelent.