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.
Also, you can make use of the MySQL JSON type if your database uses MySQL version 5.7. Updating is easy if you make use of the JSON functions such as JSON_SET. You should create a connection table to make finding the column(s) within the JSON that need to be updated faster. For example, item to size. It has been a very efficient for our team's scaling product and keeps everything organized to avoid overusing JOINS!
Data Model Resource Book, Volume 1 - this is WAY too big for a post here, it covers whole chapters in that book. Other chapters include how to model a good price structure with rebates.
The problem is that colour and size are orthogonal, not all the same for all products, and MAY have different sizes.