Need to create table relationships

Question

Need to create table relationships

ramadesai108

SSChampion

Points: 10609
More actions
January 18, 2014 at 2:36 pm

#282476

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 1

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 2

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.

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 4

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

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

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 6

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.

ramadesai108 SSChampion Points: 10609 More actions · Answer 7

Hello Greg,

Thanks for the information, that helps. Have a great day.

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 8

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 9

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramadesai108 SSChampion Points: 10609 More actions · Answer 10

Hello Jeff Moden,

What is not very clear to me is how to insert or delete data when the data is already existing in the hierarchy table. So I started researching and found this:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

So the question is what if I don't add any HLevel or NodeNumber or SortPath columns. What could be the impact later on of avoiding these columns?

Thank you for writing the article and sharing with us, it is a blessing for us.

As always, thank you for your time.

Rama

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 11

ramadesai108 (1/24/2014)
Hello Jeff Moden,
What is not very clear to me is how to insert or delete data when the data is already existing in the hierarchy table. So I started researching and found this:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
So the question is what if I don't add any HLevel or NodeNumber or SortPath columns. What could be the impact later on of avoiding these columns?
Thank you for writing the article and sharing with us, it is a blessing for us.
As always, thank you for your time.
Rama

You wouldn't be avoiding the columns. In the case of an inserted node, you'd simply add it to the Adjacency List with the correct parent ID and then rebuild the Nested Sets using the code I posted. Similar code would work for leaf level deletes. Replacing a node is simple... update the the nodes that have the same parent id as the node being replaced, at the new node, and rebuild the Nested Sets. It's a whole lot easier than writing code to do the same thing and correctly calculate the left and right Bowers in the Nested Sets.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramadesai108 SSChampion Points: 10609 More actions · Answer 12

ramadesai108

SSChampion

Points: 10609

January 27, 2014 at 7:09 am

#1683534

Thanks again, Jeff.