SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Need to create relationships in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

109 Posts

Posted - 01/17/2014 :  17:12:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1673 Posts

Posted - 01/17/2014 :  19:19:55  Show Profile  Reply with Quote
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
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 01/18/2014 :  16:33:04  Show Profile  Reply with Quote
Hi,
No there is no many-to-many relationship here. Please read my question again. Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/19/2014 :  02:37:25  Show Profile  Reply with Quote
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
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 01/20/2014 :  16:41:13  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/21/2014 :  06:28:22  Show Profile  Reply with Quote
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
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 01/21/2014 :  09:45:26  Show Profile  Reply with Quote
Hi,
Can you please tell me how are you deciding that there are multiple names?
Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/22/2014 :  08:29:49  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000