CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
user2551866
user2551866

Reputation: 223

Advice on database design for multiple catalogs

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

Answers (2)

Miller
Miller

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

spencer7593
spencer7593

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.

  • a catalog has zero, one or more products (product_sizes)
  • a product (product_size) appears in zero, one or more catalogs

The relationship table gets us "one-to-many" relationships, which we can represent:

  • a catalog_product appears in exactly one catalog
  • a catalog contains zero, one or more catalog_product
  • a catalog_product is related to exactly one product
  • a product belongs to zero, one or more catalog_product

Upvotes: 2

Related Questions