Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need to create table relationships Expand / Collapse
Author
Message
Posted Saturday, January 18, 2014 2:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
I have categories, sub-categories, sub-sub-categores and a product name. I will create Categoes table, sub-categories table and sub-sub-categories table. There may be just categories without sub-categories and there may be sub-categories without sub-sub-categories. In the products table, I will have productID and ProductName. But how do I tie the Products table with Categories?
Example Data:
Category Sub1 Sub2 Name
Diagnostic Instruments Operation/Delivery Room Surgical Instruments Konig MDS0809711 Curved Surgcal Scissor Set of 4
Diagnostic Instruments Operation/Delivery Room Surgical Instruments Alcon Monarch II IOL Delivery Injector Set of 2
Diagnostic Instruments Operation/Delivery Room Surgical Instruments Aesculap MB880R BRUNS CURETTE
Post #1532406
Posted Saturday, January 18, 2014 10:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
ramadesai108 (1/18/2014)
I have categories, sub-categories, sub-sub-categores and a product name. I will create Categoes table, sub-categories table and sub-sub-categories table. There may be just categories without sub-categories and there may be sub-categories without sub-sub-categories. In the products table, I will have productID and ProductName. But how do I tie the Products table with Categories?
Example Data:
Category Sub1 Sub2 Name
Diagnostic Instruments Operation/Delivery Room Surgical Instruments Konig MDS0809711 Curved Surgcal Scissor Set of 4
Diagnostic Instruments Operation/Delivery Room Surgical Instruments Alcon Monarch II IOL Delivery Injector Set of 2
Diagnostic Instruments Operation/Delivery Room Surgical Instruments Aesculap MB880R BRUNS CURETTE


I believe you might be making a mistake by making 3 tables for this because your description has all of the ear markings for a nice hierarchical table. I recommend an "Adjacency List" hierarchy with the understanding of what you can do with it based on the following 2 articles.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/

As for how to tie products to categories, I would imagine that a product could fit more than one category and, most certainly, a category would have more than one associated product. That would mean that you'd need a "bridge table" or "cross reference" table with a column for the category ID and a column for the product ID. Then, someone has to fill it in properly.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532426
Posted Monday, January 20, 2014 8:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 1,145, Visits: 6,242
I agree with Jeff, it appears to be a ragged hierarchy.
In your example, you have a where (physical location), and a type of item.
Will any of the items be used somewhere else? Like an Ambulance, or Emergency Room?

One way of handling this is to take and create a hierarchy where everything has a top level, but if the next level down is not defined, inherit from the parent.
Some items will likely be more generic, and some may have very specific lower levels.
You want a flexible way to handle this, as it will likely change over time.
Post #1532702
Posted Monday, January 20, 2014 7:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hello Jeff Moden,
Thank you for pointing me in the right direction. How about using HierarcyID, are there any drawbacks?

Thank you so much for your time,
Rama
Post #1532836
Posted Monday, January 20, 2014 10:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
ramadesai108 (1/20/2014)
Hello Jeff Moden,
Thank you for pointing me in the right direction. How about using HierarcyID, are there any drawbacks?

Thank you so much for your time,
Rama


I've not personally used HierarchyID nor am I likely to. If you do a web search on the subject, rumor has it that it can have some significant performance problems. Again, I can neither confirm nor deny that because I've not tested it.

I also think that using HierarchyID is a time bomb waiting to happen. It's much easier for a human to repair a damaged Adjacency List (because each node is only aware of one and only one other node, its parent) if something goes wrong than any other type of hierarchical structure. Its then a simple matter to regenerate both the hierarchical path (not to be confused with HierarchyID) and the Nested Sets after that.

There are also rumors that Nested Sets are slow but my personal experience has shown me that those rumors are a wee bit erroneous. It's normally because of a missing or bad index, non-Sargable code, flat out bad code, or some other human error rather than the Nested Sets. I suspect the rumors of slowness for the HierarchyID are because of the same reasons but, like I said, I can neither confirm nor deny those rumors. My personal preference is to continue to shy away from HierarchyID because of the possible maintenance issues.

Of course, there's the alternative to even Nested Sets that I featured in my second article on the subject of Hierarchies.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532850
Posted Tuesday, January 21, 2014 2:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi Jeff Moden,
I truly appreciate your help. I am working on the the nested set. Only issue could be adding and deleting nodes where I will have to be very careful.

Thanks again,
Rama
Post #1533346
Posted Tuesday, January 21, 2014 4:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 1,145, Visits: 6,242
Jeff's articles were very good.
Thanks Jeff!

Our hierarchy was 3 levels deep (Commodity Code) for an Item.
What was a different twist for us - we only assigned the bottom level to an Item in production.
We maintained the structure off to the side, and had a process to move items (or all items) from code to code as the structure was changed.
Class, Sub Class, and Commodity Code were all separate dimensions in the base and cube.

Yes, creating takes some thought. Maintaining takes some more thought.
Some of it might also depend on how you will use it in the end.
I could see some differences between using for an org chart type vs. Item.
Post #1533406
Posted Tuesday, January 21, 2014 5:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hello Greg,

Thanks for the information, that helps. Have a great day.
Post #1533424
Posted Thursday, January 23, 2014 9:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
ramadesai108 (1/21/2014)
Hi Jeff Moden,
I truly appreciate your help. I am working on the the nested set. Only issue could be adding and deleting nodes where I will have to be very careful.

Thanks again,
Rama


As I point out in the first article that I cited, I wouldn't even think of adding or deleting nodes directly in Nested Sets. Like I also said in that article, it's much easier for humans and code to maintain an Adjacency List and then simply regenerate the entire Nested Set. It takes only 54 seconds to do so on a million row hierarchy and less than 4 seconds on a 100,000 node hierarchy using the methods from that first article.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534331
Posted Thursday, January 23, 2014 9:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
Greg Edwards-268690 (1/21/2014)
Jeff's articles were very good.
Thanks Jeff!

Our hierarchy was 3 levels deep (Commodity Code) for an Item.
What was a different twist for us - we only assigned the bottom level to an Item in production.
We maintained the structure off to the side, and had a process to move items (or all items) from code to code as the structure was changed.
Class, Sub Class, and Commodity Code were all separate dimensions in the base and cube.

Yes, creating takes some thought. Maintaining takes some more thought.
Some of it might also depend on how you will use it in the end.
I could see some differences between using for an org chart type vs. Item.


Thanks Greg. I appreciate it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534333
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse