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.
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
@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.