Mysql – the best way to store product size and color for an Ecommerce database

database-designMySQLsqlite

I am trying to find the best way to store product size, colour for an sports store website, something similar to what amazon does, where you can choose a shoe colour and number. The problem is that a product may be a shoe shirt ball bag etc, so the sizing charts/systems to be used will all be different for different types of products. Also one product may have different sizes and different colours and some colours may not have a certain sizes or vice-versa? I am a rookie in database designing so I don't know how to approach this problem.

Best Answer

You will need to implement exclusive subtypes to handle the attributes for each product type. A (very) abbreviated example:

CREATE TABLE ProductType
(
  ProductTypeCd  CHAR(4)      NOT NULL /* Something human readable - SHOE, SHRT, BAG, BALL, etc */
 ,Name           VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_ProductType PRIMARY KEY (ProductTypeCd)
 ,CONSTRAINT AK_ProductType UNIQUE (Name)
)
;

CREATE TABLE Product
(
  ProductId      INT           NOT NULL /* If you have a code to track inventory/items, use that instead */
 ,ProductTypeCd  CHAR(4)       NOT NULL
 ,Name           VARCHAR(50)   NOT NULL
 ,Description    VARCHAR(200)  NOT NULL
 ,Price          DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_Product_Discriminated_By_ProductType FOREIGN KEY (ProductTypeCd) REFERENCES ProductType (ProductTypeCd)
 ,CONSTRAINT PK_Product PRIMARY KEY (ProductId)
 ,CONSTRAINT AK_Product UNIQUE (/* Whatever columns are necessary to prevent duplicates - if you are using a product code/inventory code for your PK this may be unnecessary */)
)
;

In this case ProductTypeCd works as a discriminator to define the subtype of Product, which we would then define like so:

CREATE TABLE ProductShoe
(
  ProductShoeId  INT NOT NULL
 ,GenderCd       CHAR(1)  NOT NULL /* M/W/U, should control through FK reference */
 ,ShoeSizeCd     CHAR(4)  NOT NULL /* 9/9D/9DD,12E, etc., should control through FK reference */
 ,ColorCd        CHAR(3)  NOT NULL /* Something human readable, RED, BLK, YEL, GRN, BLU, etc., again enforce through FK reference */
 ,CONSTRAINT FK_ProductShoe_Is_Product FOREIGN KEY (ProductShoeId) REFERENCES Product (ProductId)
 ,CONSTRAINT PK_ProductShoe PRIMARY KEY (ProductShoeId)
)
;

CREATE TABLE ProductShirt
(
  ProductShirtId   INT      NOT NULL
 ,GenderCd         CHAR(1)  NOT NULL /* Same treatment as for Shoe */
 ,ShirtTypeCd      CHAR(4)  NOT NULL /* TEE, POLO, etc. */
 ,ShitSizeCd       CHAR(4)  NOT NULL /* XS/S/M/L/XL/XXL/etc., again, use FK */
 ,ColorCd          CHAR(3)  NOT NULL /* Same treatment as for Shoe */
 ,CONSTRAINT FK_ProductShirt_Is_Product FOREIGN KEY (ProductShirtId) REFERENCES Product (ProductId)
 ,CONSTRAINT PK_ProductShirt PRIMARY KEY (ProductShirtId)
)
;

There is some care that must be taken to ensure the tables align with the proper ProductType. Depending on the DB engine this would have to be handled via a trigger instead of a check constraint. This SO answer handles it in depth: https://stackoverflow.com/a/4898314/13942986

From comments:

@Eniola so currently I am on the planning/design phase and I will be using a relational sql database thinking of using SqlLite or Mysql

SQLite is not a relational database, MySQL barely so. Between the two, use MySQL.

Related Question