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

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

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

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

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

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

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

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

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 (categories and subcategories (was "Database Design"). 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 .)

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

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

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?

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

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