CODEWITHSUNDEEP
databasedatabase-designrelational-database
TripleDeal
TripleDeal

Reputation: 736

Best way to design a database for product varients

I'm trying to redesign my database and I'd like to follow the normalization rules, but I can't figure out how I should store product variants.

My current database design:

Current database design

So let's say I'd like to display products on my website and every product is connected to a platform.

The following are the available platforms:

  1. PS4
  2. PS3
  3. Xbox One
  4. Xbox 360
  5. PC

So for example I have Far Cry 5 registered as a product and its connected to PS4, but there is also a PC and Xbox One version available, PS3 and Xbox 360 doesn't have its own version so those 2 are left out.

What would be the best way (with normalization included) to store this in the database?

So you'll have situations where a product only has 1 platform or just 3 platforms, but 2, 4 and 5 platforms is also possible.

In the end, I'd like to provide users with a link to each variant of a product on the product's page, if product has more than 1 variant.

Thanks for your help, it's much appreciated.

Edit:

My test database with its data:

enter image description here

Upvotes: 0

Views: 93

Answers (1)

Mihai Chelaru
Mihai Chelaru

Reputation: 8187

You could create a join table of product and platform as below.

enter image description here

You can then execute the following select statement to obtain the platformNames for any given productID:

SELECT pp.productID, pp.platformID, pl.platformName FROM Product_Platform pp 
    JOIN Platform pl ON pp.platformID = pl.platformID WHERE productID = 123456;

Upvotes: 1

Related Questions