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
 Transact-SQL (2012)
 Two Field Combos as a Primary Key?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DatabaseStudent
Yak Posting Veteran

61 Posts

Posted - 06/06/2014 :  20:12:26  Show Profile  Reply with Quote
I am looking for input on a table structure. So I have a product. This product can fit into a number of categories. For this example, we can assume that 10 is the max.

Is it suggested that I have 2 columns or 10 columns? Under two columns, every product and category would be a unique combination? In Access, I would define the field combinations as a key together. I am not exactly sure how SQL would handle that.

The other would be that each product has its own row, but only the number of categories that they are in would be populated. I don't like this option since it would probably create difficulty in querying the data.

In the first method, I could query all categories based on the product. Can someone verify that there won't be any issues with the keys if there was a ProductID and CategoryID where ProductID could be duplicated and CategoryID could be duplicated, but the combination could never be duplicated?

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 06/07/2014 :  00:15:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
Given the info you provided, I would do 3 tables: Product, Category and perhaps Product_Category. Product_Category would have ProductID and CategoryID as the PK. You might have other columns in that table, such as Description, CreatedDate, etc.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

DatabaseStudent
Yak Posting Veteran

61 Posts

Posted - 06/07/2014 :  05:18:00  Show Profile  Reply with Quote
What happens if I am referencing two of the same field?

For example:
ProductID and then other descriptor columns in ProductsTable

and

ProductID and ProductCompID in ProductComparison table?

ProductID and ProductCompID would both be references to ProductIDs but work as a combination. This would allow easiest access to get all of the comparative products to a product, correct?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 06/08/2014 :  12:58:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
I would need to see some sample data to help or the possible table layouts. I'm having a hard time following your descriptions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

DatabaseStudent
Yak Posting Veteran

61 Posts

Posted - 06/08/2014 :  14:39:25  Show Profile  Reply with Quote
Two Tables

Simplified Products Table
ProductID, Description
1, Description
2, Description
3, Description

2nd Table, Product Comparisons Table
ProductID, ProductCompID
1, 1
1, 3
2, 2
3, 1
3, 3

So this table will be able to find all comparisons to Product 1. In this case, that is only Product 3. If there are many products with many comparisons, then each combination should do the trick. However, if there is a more efficient way to handle product comparisons I would like to know. ProductCompID is just a 2nd ProductID field with the comparison to the first product. I could run a query to get the Product and list of comparable products by joining on the ProductID field and pulling all items in ProductComp field including the compared product itself.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 06/08/2014 :  18:52:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
Looks good to me.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
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