Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2006
    Location
    Shropshire, UK
    Posts
    3

    hierarchical relation

    Hi, i am having a little trouble understanding how to relate tables.

    i am looking to make my website have an input form and record display, to make it a little dynamic.
    i want to have somthing like what you get in ebay to select the section you sell somthing or the employee type of thing ( Site -> building -> section -> department -> employee ) or now that i notice:
    dBforums > General > Database Concepts & Design >

    how does a post get linked to each of the sections of the forum?

    i have made a start but im not sure if im going in the right direction or of there is a better way to do it?
    http://i50.photobucket.com/albums/f3...H/Capture4.jpg

    Thankyou for help

    Wayne
    Attached Thumbnails Attached Thumbnails Capture4.jpg  
    Last edited by wkd; 04-04-06 at 20:48. Reason: bad title

  2. #2
    Join Date
    Mar 2006
    Location
    Danbury, CT USA
    Posts
    6
    I think you're on the right track. Here's an example:

    table1 - category_primary
    primary_id (PK)
    category name

    table2 - category_secondary
    secondary_id (PK)
    primary_id (FK)
    categoryy name

    table3 - category_tertiary
    tertiary_id (PK)
    secondary_id (FK)
    category name

    table4 - products
    product_id (PK)
    product name
    secondary_id (FK)
    tertiary_id (FK)
    etc...


    this allows you to have a top level category with 2 sub-levels. A product can be associated with either of the 2 sub-levels.

    Hope this helps.

    --Ken

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Can this method be generalized?

    Ken,

    Your approach is a good attempt. There is a limitation, though, as you yourself noted, that this caters only to one top-level and two sub-categories.

    Is it possible to generalize this method so that no matter the number of sub-sub-...sub-categories, we can use the model?

    The reason I am asking is because most applications eventually require an arbitrary number of sub-categories.

    Ravi

  4. #4
    Join Date
    Mar 2006
    Location
    Danbury, CT USA
    Posts
    6
    Well, this method might work:

    table: categories
    category_id (PK)
    name

    table: heirarchy
    parent_id (FK)
    child_id (FK)

    Both of the FK's in the heirarchy table will reference the category_id in the categories table. So, you'll have ALL of you categories regardless of place in the hierarchy in the categories table. Your heirarchy is defined in the second table. See some example data:

    categories table:
    category_id = 101 | name = automobiles
    category_id = 102 | name = SUV
    category_id = 103 | name = coupe
    category_id = 104 | name = sedan
    category_id = 105 | name = midsize
    category_id = 106 | name = large
    category_id = 107 | name = boats

    heirarchy table:
    parent_id = 101 | child_id = 102
    parent_id = 101 | child_id = 103
    parent_id = 101 | child_id = 104
    parent_id = 104 | child_id = 105
    parent_id = 104 | child_id = 106

    This shows that SUV, coupe, and sedan are children of automobiles.
    midsize and large are children of sedan.
    boats has no children and is therefore not in the heirarchy table.

    --Ken

  5. #5
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Yes, that's it.

    Yes, that's what I was looking for.

    Notice that the more general method requires fewer tables than the initial design that had some limitations, yet is more flexible and powerful than the initial design. Also, when you work out the code to express the hierarchy, you'll find that, once written, you won't have to change it; unlike the initial solution where every layer of sub-category added would have required one to change the code.

    This is also a point that XP (eXtreme Programming) advocates would have missed because they would have done "the simplest thing that works" and created rigid, high maintenanc code.

    I think that it pays off to spend a little bit of time thinking about the problem and trying to anticipate the types of changes that might occur. Such thinking will lead to robust, low maintenance, more powerful systems, I feel.

    Ravi

  6. #6
    Join Date
    Mar 2006
    Location
    Danbury, CT USA
    Posts
    6
    hmmmm... I get the feeling that you knew that answer all along... Well, that's OK, you DID make me think. So, it seems to me that this table structure will do the job, but I wouldn't want to have to think up the SQL to extract the data. I imagine that this sort of thing has already been written a dozen times. Any suggestions on where to find sample code? or even sample data structures, for that matter. Many concepts are similar, and it would be helpful if there were a repository of examples for people like me to browse.

    --Ken

  7. #7
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Try googling for "adjacency list model" for some pointers and design help. You may also want to check out "nested set model."
    http://www.developer.com/db/article.php/3517366

  8. #8
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Nested Set Model seems to offer no benefits

    I've read the article on Nested Sets referred to by jfulton (http://www.developer.com/db/article.php/3517366).

    The following discussion refers to that article and assuems that the reader has read it.

    I am not convinced that it is a better representation of hierarchy than the traditional representation using a single table with parent_id and child_id which is an implied tree.

    There are several issues with the nested sets approach that I see:
    • There is an explicit dependence on the presentation of the data. Why should the ids have to depend on the order in which the data is presented? For example, if I want to show Tennis data before Golf data, what would need to change in the data? Quite a lot, I guess.

      An important idea in relational database theory (set theory) is that the ordering of the data, or the columns within the data, is unimportant. Some consequences of violating this stricture are explained below.
    • What if two or more applications need to present the data in different order? Should we store the data multiple times? How do we distinguish which data (and ordering) is for what application?
    • The simple operations of insert and delete become much more complex, unnecessarily so.
    • One aim of normalization is to ensure that there are no transitive dependencies between columns (attributes) in the same row (tuple). Nothing is mentioned about rows depending upon other rows. Presumably this is because this is such an appalling idea that practitioners never thought that one would have to state this rule explicitly. Rows ought to be independent of each other.

      Yet, the columns named "left" and "right" depend on at least two other rows for their values! So, if any of the rows changes, we have to look at other rows to see what cascading effect it will have. So, a simple update or insert into a table could result in a scan of the whole table! Seems wrong, simply wrong.
    • What are the performance implications if this technique were used to represent the products that a large company like Wal-Mart has? My guess is that they have tens of thousands of products in thousands of specialized categories. One simple deletion would probably end up requiring changes to hundreds, even thousands of rows. It is not logical to expect that deleting a row in a table would necessitate changes to other rows in the same table.

      That is ugly!
    All I can say is that I prefer the simpler version that we've been using. There seems to be no compelling reason to start using the nested sets model.

    Just my thoughts.

    Ravi
    Last edited by rajiravi; 04-07-06 at 15:14.

  9. #9
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    I totally agree with you Ravi. That link was just to show some other options to the database design.

    I personally don't like using the nested set OR the adjacency model as I tried to explain in a similar thread in the mysql forum (http://www.dbforums.com/showthread.php?t=1214098). I've just encountered too many problems with them in the past. But, when you need to enforce strict relationship cardinality, then one of those two models is usually the simplest way to go (although I do still like my own method better .)

  10. #10
    Join Date
    Apr 2006
    Location
    Shropshire, UK
    Posts
    3
    Thanks for the reply I have been looking at the model from porpie so far,
    Just trying to figure out what happens when I add more data and categories like mileage and year. (Based on the automoto theme)

    When I have the structure complete how do like the actual data to each of the categories? Do I have to enter the parent child relationship for every unit/item/product?

    In the mean time I will keep reading posts and the intergoogle

    Thank you

    Wayne
    Attached Thumbnails Attached Thumbnails data2.jpg   data1.jpg  
    Last edited by wkd; 04-08-06 at 08:48.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    extra categories for mileage? no, mileage would be a data attribute

    let's say you had a table of people, and you wanted to categorize them based on their location

    the location categories would have a hierarchy (continent > country > state > city ) but you would link each person only to the lowest level in the hierarchy, yes?

    i mean, you wouldn't link Todd to Los Angeles and also to California -- it's not necessary, because Los Angeles is a subcategory of California

    mileage, on the other hand, is different

    if a particular car has 23,937 miles, would you have a category for that? would you have a category for 20,000-30,000 miles? then a super-category for 0 - 100,000 miles (which contains the subcategory 20,000-30,000 as one of its subcategories)?

    no

    any time you want to search for cars with a specific mileage, you'd simply search the cars with a BETWEEN clause


    similarly with colours -- in one of your sketches you have colour as a subcategory of automobile

    colour isn't a hierarchy, it's just a data attribute like mileage

    in my opinion, a category or similar hierarchy must be homogeneous, and consist of only one characteristic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2006
    Location
    Shropshire, UK
    Posts
    3
    the location categories would have a hierarchy (continent > country > state > city ) but you would link each person only to the lowest level in the hierarchy, yes?
    To be able to select from a list on an input form linked to a database for the location example would the hierarchy table have to contain every continent/country/state/city all in one table? And then how would I link my person_ID table to the lowest thread in the hierarchy? Is this where the nice SQL comes in to make it all work?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wkd, yes, all one table

    create table categories
    id (PK)
    parent_id (FK)
    name

    101 null asia
    102 null africa
    103 null north america
    401 101 china
    405 103 usa
    511 405 california
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Another alternative

    There is another alternative to the one suggested by Rudy (r937). This has already been suggested by porpie (Ken).

    Table region (id, name, description)
    Table region_hierarchy( region_id, parent_region_id)

    Both will work. My personal preference is the one I've shown above mainly because it adds a bit more flexibity. It is possible to have a sub-category belong to more than one category. Not that this is required in a geographical category. There are exceptions though - does Russia belong to Europe or Asia given that it lies in both Asia and Europe? For other things like a product hierarchy, this flexibility may be required. An electric kettle could belong to both the "Household Items" and "Small Electricals" category.

    Another reason I like this design is because it avoids nulls and I try to avoid them whenever I can. Many practitioners seem to have no problems with nulls, though.

    A third reason I like this approach is simple - you can add information to the hierarchy table that is only relevant to the association, not necessarily to the regions. For example, I can add the fields "effective_date" and "end_date" to the region_hierarchy table to show when the association of the two regions became effective. With countries being created from larger countries not so infrequently, this may prove to have some benefits. For example, not so long ago, Canada added Nunavut to its list of provinces/territories; and India broke up some of the larger states into smaller ones. Incidentally, some of the newly created smaller states in India have a population larger than that of Canada! And if things go as planned by the US think tank, you may have to define Iraq twice, once as the 51st state of the USA (or is it 52nd, after Great Britain?), and once as a pseudo-independent country. Then we would have the strange case of Iraq being in North America and Asia! Just kidding!

    Ravi
    Last edited by rajiravi; 04-08-06 at 22:56.

Posting Permissions

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