Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldev80
Yak Posting Veteran
68 Posts |
 Posted - 2006-06-15 : 19:36:35
|
| I have a table:Product_id(parent_id), product_ref_id(child_id), product_name2 0 clothes3 2 pants4 2 shirts5 2 coats6 3 jeans8 6 boot cutNow here for each product_id there can be only one product_ref_idI want to change my table, maybe create another table so thatI can have design like this when people search my siteClothes--pants--jeans--boot fitClothes--pants--jeans--relaxedor just likejeans---relaxedjeans---loose fitor shirts------long sleeves-cottonclothes---shirts---long sleeves---denimshirts---black---cottonso jeans being a child of clothes can also be a parent directly.So each parent can have multiple children and each child multiple children. and each child can have further children.But my current table restricts each child to have only one parent and no further children.I can create another table with colums parent_id, child_id Any advice or suggetions on this one. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-06-15 : 21:22:08
|
| All you need is:ID, Product_Name, ParentIDand you can have many children per parent. As long as one child cannot have more than 1 parent, you are all set.You just had it backwards.Are you using SQL 2000 or 2005? If it is 2000, you may find that it can be difficult sometimes to query data that is stored in a hierachy. very easy in 2005. |
 |
|
|
sqldev80
Yak Posting Veteran
68 Posts |
Posted - 2006-06-15 : 21:24:01
|
| Well the child can have more than ONE parentlikesay tennis skirt is one item it will be under apparel and as well as sports like this clothes----women----dress---tennis dress sport---apparel---women----tennis dress there can be colors too which can be any ones child or parent sport---apparel---women-----red-------tennis dress clothes----women----red-----dress---tennis dress OR clothes----women----dress-----RED------tennis dress OR Sale-----women----tennis dress |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-06-15 : 22:46:57
|
| I think you will end up with a big mess if you don't enfore a single, clear, consistent structure. It is important to establish a difference between an Item's location in some navigation tree or hierachical structure, versus attributes that it possesses which you might choose to search on or sort by. Should things like color and gender and so on be all over the place in the tree, or should they be consistently defined? |
|
|
|
sqldev80
Yak Posting Veteran
68 Posts |
Posted - 2006-06-15 : 23:06:22
|
| well i think they should be consistenly defined but the database should be designed so that front end can be able to design it according to any need one particular structure which will define all the parent and child relations |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
converting n-1 relation to n-n : interesting