Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Storing hierarchical data in MySQL

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by vikram8jp
    Every category listed above has a table to itself and stores the attributes for that particular category.
    Why aren't these items all be in the hierarchy table to?

    Quote Originally Posted by vikram8jp
    How should I store my hierarchical data?
    We had a good discussion on different ways to store hieracical data here.

    Quote Originally Posted by vikram8jp
    Should I use nested sets --> I am not sure if this technique can be applied, since there are going to thousands of items,
    Why would this be an issue?

    Quote Originally Posted by vikram8jp
    there is no one root node.
    What's wrong with using item or vehicles as the root node?

    Quote Originally Posted by vikram8jp
    -> spare parts
    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.

  3. #3
    Join Date
    Jun 2009
    Posts
    8
    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?

    Quote Originally Posted by mike_bite_kite
    Quote Originally Posted by vikram8jp
    -> spare parts
    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.
    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
    Attached Thumbnails Attached Thumbnails diagram.JPG  

  4. #4
    Join Date
    Jun 2009
    Posts
    8
    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.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mike_bike_kite
    Your diagram is very small for my poor eyes!
    "CTRL" + "+", in FF
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  8. #8
    Join Date
    Jun 2009
    Posts
    8
    Hello Mike...thanks for helping me out.

    Quote Originally Posted by mike_bite_kite
    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 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.

    Quote Originally Posted by mike_kite_bite
    The Parts table could still be kept separate. Odd that this table doesn't have a model field or a price field though.
    Oh, I did not send the complete database spec since it was too big to be convenient.

    Quote Originally Posted by mike_kite_bite
    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.
    Sorry about that.

    Thanks and Regards
    Vikram

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by vikram8jp
    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.
    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.

    Quote Originally Posted by vikram8jp
    Oh, I did not send the complete database spec since it was too big to be convenient.
    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!

  10. #10
    Join Date
    Jun 2009
    Posts
    8
    Quote Originally Posted by mike_bite_kite
    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.
    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.

    Quote Originally Posted by mike_bite_kite
    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!
    Cheers

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by vikram8jp
    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.
    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?

    Quote Originally Posted by vikram8jp
    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.
    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.

  12. #12
    Join Date
    Jun 2009
    Posts
    8
    Hello Mike

    Quote Originally Posted by mike_bite_kite
    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.
    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
    Attached Files Attached Files

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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....
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Let's keep this topic on track people - I think Mike is making excellent progress.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •