Reputation: 223
I have a relatively simple database design that I will simplify here for the sake of brevity.
I have 4 tables => Products, Product_Sizes, Stores, Catalogs
.
There are 6 stores and each store has it's own unique customized catalog, an assortment of different products and sizes that is chosen from the Products
and Product_Sizes
tables.
I am wondering how to best design the Catalogs
table. My ideas are:
id store_id product_id product_size
1 1 53522 1
2 1 40299 1
3 2 43326 1
4 2 43326 2
OR
id store_id product_id product_sizes
1 1 53522 1
2 1 40299 1
3 2 43326 1,2
Each store has it's own unique (and only one) catalog, so a query to fetch all entries by store_id will result in that store's catalog.
Another approach would be to create another table of the combined products and product_size into it's each own unique table, let's call it Products
.
id product_id product_size
1 1 1
2 1 2
3 1 3
4 1 4
This separate table gives me a unique id for all the possible products and their size variants. This would result in a single id per product for each Catalogs
entry.
I would love to hear some critique and better suggestions as I feel this just isn't right and can't put my finger on how to better design this. Also, if we were to ever implement more than 1 catalog per store, I know this current design would cause me grief. Any feedback would greatly appreciated!!
Upvotes: 1
Views: 754
Reputation: 1156
id store_id product_id product_size
1 1 53522 1
2 1 40299 1
3 2 43326 1
4 2 43327 2
Different product id for same product name with different sizes.
Upvotes: 0
Reputation: 108530
If it were me, I would create the catalog
table as an entity table, with it's own id. If that catalog only belong to one store
, I would add a store_id
column as a foreign key.
I would then add a catalog_products
table. This would have a foreign key to the catalog
table, as well as a foreign key to the products
and/or product_sizes
table. (If a product_size
belongs to only one products
table, then just the products_sizes
table.)
To resolve a many-to-many relationship, we typically add a third relationship table.
The relationship table gets us "one-to-many" relationships, which we can represent:
Upvotes: 2