#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    226
    Rep Power
    6

    A Shop can have Products from a Supplier


    I'm creating a Platform where Suppliers have Products that Shops can choose, add to their Shop and start selling.

    My situation is as follows:

    - A Supplier can have many Products (for example: 'T-shirt', 'Iphone Case') (**Supplier->product: One-To-Many**)
    - A Supplier Product can have many SKU (for instance: 'T-shirt Small Black', 'T-Shirt Small White') (**Product->sku: One-To-Many**)
    - A Shop should be able to "copy" Products from the Supplier, So would I need a Shop_Product table here? (**Shop->Product: One-To-Many**)


    The main problem is that my Shop has SKU's from the Supplier but no Products. If I add a Shop_Products table representing a relationshhip, then how should I "couple" my Shop_Skus to that table?

    Should I add a Shop_Product table, next to the existing Shop_Sku table?

    My current database structure is depicted in the diagram below:
    W9Qy7.png

    With a Shop_Product column it would look like this
    PX7Cw.png

    But then there is no relationship between the Shop_Product and the Shop_Sku which makes this structure irrelevant.

    Any help is welcome!
  2. #2
  3. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,453
    Rep Power
    9645
    No. A SKU already identifies a Product, so if you want to know the Products that a Shop has you go through the SKUs table. Adding a Shop <-> SKU table creates redundancy and thus risk of inconsistencies.
    Code:
    SELECT products.id
    FROM shop_skus
    JOIN skus ON shop_skus.sku_id = skus.id
    JOIN products ON skus.product_id = products.id
    WHERE shop_skus.shop_id = 123
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    226
    Rep Power
    6
    Thanks for your answer! The issue is that I'm filling the shop_skus table with a lot of data.
    And for example If I want to delete a shop_sku I don't have a unique identifier to work with..
  6. #4
  7. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,453
    Rep Power
    9645
    Sure you do: shop_sku + sku_id is unique. But if you really want you can add an artificial primary key of just an auto_incrementing ID.

IMN logo majestic logo threadwatch logo seochat tools logo