1

I have a project where I have Products and SKU's. Each Product has one or many SKU's.

I also have Shops that can have many to many Products, So a Product can belong to multiple Shops and a Shop can have multiple Products.

To finish, each Shop can choose which SKU's they want for which Products. So I as a Shop can choose to sell the Tshirt White, which may have (XS,S,M,L) and I can choose (XS,S) as SKU's I want for my Shop.

Currently

As you can see A Shop is connected to the Products, and to the SKU's. But I have a feeling there's something wrong

My shop_skus and shop_products are not connected, So there can be a mismatch between the products a shop has, and the sku's..

How would I go about fixing that?

Thank you for your time!

enter image description here

Improve this question
7
  • It is fine. And it isn't. It depends on your requirements. Do you allow a sku to be available for a shop that doesn't have the (related to this sku) product? If yes, everything is good.
    – ypercubeᵀᴹ
    Commented Jul 31, 2017 at 20:07
  • I'm affraid not, The SKU's should always match the shop_products.. That's the big issue, thanks for your time
    – Miguel Stevens
    Commented Jul 31, 2017 at 20:09
  • 1
    Then (one way to do this and the only one I know with DDL) is to drop the relationship from shop_skus to shops and make it from shop_skus to shop_products instead. You will also need to add product_id in shop_skus for this to work and a few more minor modifications (2 of the FKs will be composite). See a similar problem: Data inconsistency prohibition if a table refers to another via two many-to-many relationships and my answer there.
    – ypercubeᵀᴹ
    Commented Jul 31, 2017 at 20:12
  • 1
    Translation: (Realm=shop, Project=sku, Company=product)
    – ypercubeᵀᴹ
    Commented Jul 31, 2017 at 20:18
  • I understand! I another thread someone proposed to add another model such as CarriedProduct, that would then replace the need for a diamond connection, what do you think about that?
    – Miguel Stevens
    Commented Jul 31, 2017 at 20:50

1 Answer 1

Reset to default
2

Edit: I didn't notice that this has been suggested already in comments by ypercube. So crediting him here.

Ultimately its up to you and your exact requirements. But here are following suggestions:

  1. SKUs does not exists if Products does not exist. In other words, you cannot make a SKU as a standalone entity. The basic entity therefore is Products. And as a Product can have multiple SKU, make this relationship many to many.
  2. So if you follow point 1, You can safely remove shop_skus relationship because now, a shop can select products via shop_products and as products are in many-many relationship with SKU you can enable shop to pick suitable SKU directly using suitable query.
Improve this answer
2
  • Thanks for your answer, I'm wondering how I can still have a Shop select which SKU's of a product they want..
    – Miguel Stevens
    Commented Aug 1, 2017 at 6:06
  • By using inner joins in your sql queries you can definitely do so.
    – Karan Desai
    Commented Aug 1, 2017 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.