Need to create relationships in a table

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.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Need to create relationships in a table

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2014-01-17 : 17:12:44

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-17 : 19:19:55

Is it true that a Product can belong to multiple Categories and that a Category can include multiple Products? If so, you have a "many-to-many" relationship. You can implement this by creating an intervening table that holds that Product and Category keys. This type of table has different names but I've often heard referred to it as a "junction table". The table might hold only these two keys but it could also hold other attributes of the relationship. If Categories and Sub-categories follow the same pattern, they would also need a junction table; ditto sub-sub-categories. This does make for some big hairy join code.

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

rama108
Posting Yak Master

115 Posts

Posted - 2014-01-18 : 16:33:04

Hi,
No there is no many-to-many relationship here. Please read my question again. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-19 : 02:37:25

quote:
Originally posted by rama108

Hi,
No there is no many-to-many relationship here. Please read my question again. Thanks.

Same as
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190797
why open multiple threads for same issue?
Also FYI your question doesn't have any information on whether many to many is there or not. So you need to either clearly specify us how relationship is or show us some sample data to illustrate it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rama108
Posting Yak Master

115 Posts

Posted - 2014-01-20 : 16:41:13

Hello,
I already provided data in my 1st post:

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

In the above data, "Diagnostic" is category
"Instruments Operation/Delivery Room" is Sub1 category
"Surgical Instruments" is sub2 category
"Konig MDS0809711 Curved Surgcal Scissor Set of" is the name of the product

there is no many-to-many relationship between categories.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 06:28:22

quote:
Originally posted by rama108

Hello,
I already provided data in my 1st post:

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

In the above data, "Diagnostic" is category
"Instruments Operation/Delivery Room" is Sub1 category
"Surgical Instruments" is sub2 category
"Konig MDS0809711 Curved Surgcal Scissor Set of" is the name of the product

there is no many-to-many relationship between categories.
Thanks.

the posted data shows multiple names under same category subcategory hierarchy. can you show fields available on those category subcategory etc tableS?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rama108
Posting Yak Master

115 Posts

Posted - 2014-01-21 : 09:45:26

Hi,
Can you please tell me how are you deciding that there are multiple names?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 08:29:49

quote:
Originally posted by rama108

Hi,
Can you please tell me how are you deciding that there are multiple names?
Thanks.

from your sample data

ie below values


Konig MDS0809711 Curved Surgcal Scissor Set of 4
Alcon Monarch II IOL Delivery Injector Set of 2
Aesculap MB880R BRUNS CURETTE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources