Clever Geek Handbook

MySQL Double many-to-many on Entity

I have a project where I have Products and SKUs . Each product has one or more SKUs.

I also have Stores that can have many, many products, so a product can belong to several stores, and a store can have several products.

To complete, each store can choose which SKU they want for which products. So I, as a store, can choose to sell Tshirt White, which can have (XS, S, M, L) and I can choose (XS, S) as the SKU I want for my store.

Currently

As you can see, the Store is associated with Products and SKUs. But I feel that something is wrong.

My shop_skus and shop_products are not connected, so there may be a discrepancy between the products that are in the store and the skers ..

How can I fix this?

Thank you for your time!

enter image description here

+3


source to share


2 answers


If every store has a set of products and every store-to-product relationship has a set of SKUs, then I think you need to use an intermediate model to handle the relationship. You can create a model named CarriedProduct

that has a store, product, and one-to-many relationship with the SKU.



It is common to add additional information to the relationship, i.e. Shop-> Product, you have to use pivot columns in relationship table. But since there are multiple skus, the pivot columns won't cut it.

+2


source


My shop_skus and shop_products are not related, so there might be a mismatch between the products that are in the store as well

From what I understand, you have two indirect relationships between stores and products: shops -> shop_skus -> skus -> products

and shops -> shop_products -> products

. The consistency issue can be fixed by using composite foreign keys.

  • Add column shop_skus.product_id

  • Define composite foreign keys:
    • shop_skus(product_id, sku_id) -> skus(product_id, id)

    • shop_skus(shop_id, product_id) -> shop_products(shop_id, product_id)

You can change the order of the columns in the keys. It depends on which indexes are best used for your queries.

With these foreign keys, you can only insert shop-skus that have rerlation for both tables: shop_products

and skus

.

Update

If I understand your comments correctly, you need a link between SKU stores and grocery slugs.



Example: Some stores sell

product     | slug        | SKUs
------------|-------------|-----
white shirt | Batman      | M,L
white shirt | Wonderwomen | XS,S

In this case, I would do the following:

  • Add AUTO_INCREMENT PRIMARY KEY shop_products.id

  • Add column shop_skus.product_id

  • Add column shop_skus.product_shops_id

  • Define composite foreign keys:
    • shop_skus(product_id, sku_id) -> skus(product_id, id)

    • shop_skus(product_id, product_shop_id) -> shop_products(product_id, id)

You can / should also leave the following foreign key if it exists:

  • shop_skus(sku_id) -> skus(id)

Since that would be superfluous.

+2


source






More articles:


All Articles