0

I have these following tables:

platforms (
    id [PK]
    name
)

shops (
    id [PK]
    name
)

products (
    id [PK]
    name
    description
    price
)

I need to store the data between shop and platform where they have many to many relation, exp:

Shop1 Platform1 Shop_Link
Shop1 Platform2 Shop_Link
Shop2 Platform1 Shop_Link
Shop2 Platform2 Shop_Link

So i create a table:

shop_platform (
    shop_id [PK][FK]
    platform_id [PK][FK]
    shop_link
)

now i have a case where, each product can have link for the platforms depends on which shop the product chosen, plus each product can have many shops and vice versa. exp:

Product1 Shop1 Platform1 Product_Link
Product1 Shop1 Platform2 Product_Link
Product1 Shop2 Platform1 Product_Link
Product1 Shop2 Platform2 Product_Link
Product2 Shop1 Platform1 Product_Link
Product2 Shop1 Platform2 Product_Link
...etc

my idea is to create new table which include product, shop, and platform:

shop_platform_product (
    shop_id [PK][FK]
    platform_id [PK][FK]
    product_id [PK][FK]
    product_link
)

but i'm not sure is this a best approach or not, since this is my first time to have this problem.

my question, is this a valid approach? is there other approaches that people commonly used?

2 Answers 2

1

Given that I have a correct understanding of your requirements, your approach is perfectly valid.

  • shop_platform records the fact that a specific shop is present on a specific platform.

  • shop_platform_product records the additional fact that a product is offered by a specific shop on a specific platform that the shop is present on.

Now you may want to consider recording the shop-product relationship as an additional fact, before relating the product to a specific shop's platform. Depending on your exact requirements, it may be necessary to do so too (e.g. is the product's price not shop-specific?). Even if not out of necessity, you may find it more logical and future-proof to do so.

data model

4
  • Thank you for answering, in your example, the ShopPlatformProduct is connected to ShopProduct and ShopPlatform, but can i directly connect it to platform, shop, and product instead?
    – Luna
    Dec 11, 2023 at 14:08
  • You are welcome. What do you mean exactly, completely remove ShopProduct and ShopPlatform? Or not reference them from ShopPlatformProduct? Dec 11, 2023 at 19:46
  • Oh, what i meant was in your diagram the ShopPlatformProduct ShopId, PlatformId, and ProductId referencing the ShopPlatform and ShopProduct table right? but can i change the foreign key references of ShopId, PlatformId, and ProductId from ShopPlatformProduct to each individual master table (Platform, Shop, Product) instead?
    – Luna
    Dec 12, 2023 at 4:27
  • No, it is not the same. For a (ShopId, PlatformId, ProductId) in ShopPlatformProduct, the Foreign Key constraints ensure that there is actually a (ShopId, PlatformId) in ShopPlatform and a (ShopId, ProductId) in ShopProduct. If you go and reference the top tables you lose all this relational integrity, and the data in ShopPlatformProduct will not be constrained to actually related Shops,Platforms and Products. Dec 12, 2023 at 8:51
0

After creating platforms, shops, products and shop_plaforms tables just create a relationship from products table and set the product_id as a foreign key (no need to set it as pk)

and you don't need for a forth table

 TABLE `shops`
 
  `shop_id`   PK,
  `shop_name` 
 
 

 
   Table platforms

 
  `platform_id` PK,
  `platform_name` 
 

 

  Table products

  
 
  `product_id` PK,
  `product_name`  
 
 
   Table shop_platform

 
  `shop_id`   PK FK,
  `platform_id`  PK FK,
  `product_id` FK
 

So the records in shop_platfrom table should be like this

shop1 platform1 product1

shop1 platform2 product1

shop2 platform1 product1

shop2 platform2 product2

No redundancy here!

2
  • 1
    Thank you for the answer, but i think i still need the product_id to be PK because if not there will be only one type of product in one shop - platform , example i can't insert shop1 platform1 product2, bacase shop1 and platform1 already exists as PK.
    – Luna
    Dec 11, 2023 at 14:13
  • So what about making product 1 PK FK
    – PHPFan
    Dec 12, 2023 at 12:04

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.