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.
| Author |
Topic |
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
 Posted - 2014-06-06 : 20:12:26
|
| 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
38200 Posts |
Posted - 2014-06-07 : 00:15:30
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-06-07 : 05:18:00
|
| What happens if I am referencing two of the same field?For example:ProductID and then other descriptor columns in ProductsTableand 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-08 : 12:58:22
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-06-08 : 14:39:25
|
| Two TablesSimplified Products TableProductID, Description1, Description2, Description3, Description2nd Table, Product Comparisons TableProductID, ProductCompID1, 11, 32, 23, 13, 3So 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Two Field Combos as a Primary Key?