patternsqlModerate
Database Design (Stores + Products)
Viewed 0 times
databasedesignstoresproducts
Problem
I currently have 2 tables in my DB, stores and products. Each product has it's own ID 1-2000, and each store has its own id 1-200.
My question on DB design, is how do i keep track of which products each store has?
-
example
-
Should I have a column in my stores table that has id's stored as a string separated by a column such as {1;5;10;20} (with the possibility of thousands of entries in the 1 string) ?
-
Should I have another table that is just [id|storeID|productID] (with the possibility of stores(x)products in the table) ?
My question on DB design, is how do i keep track of which products each store has?
-
example
- store 1 has products 1, 5, 10, 200
- store 2 has products 2, 5, 7, 100
- store 3 has products 1, 7, 10, 300
- etc...
-
Should I have a column in my stores table that has id's stored as a string separated by a column such as {1;5;10;20} (with the possibility of thousands of entries in the 1 string) ?
-
Should I have another table that is just [id|storeID|productID] (with the possibility of stores(x)products in the table) ?
Solution
The first option you present is a "NoSQL" design pattern & doesn't allow for easy querying in a normal RDBMS.
Your second option is the most suitable. Have a 3rd table that tracks which products are in which store. Possibly called "Stock" - you could add a count to this if you wanted to track how many of each item each store has.
Your second option is the most suitable. Have a 3rd table that tracks which products are in which store. Possibly called "Stock" - you could add a count to this if you wanted to track how many of each item each store has.
Context
StackExchange Database Administrators Q#21925, answer score: 10
Revisions (0)
No revisions yet.