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.
-
Do you have a preliminary design that can be enhanced? And what database do you want to use for storage?– EniolaOct 8, 2020 at 12:53
-
@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– Yuran PereiraOct 8, 2020 at 13:27
3 Answers
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.
-
1
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!
-
Is there no downside to it, such as lower execution speed for queries? Sep 13 at 22:20
-
@RohitGupta We did experience some downsides when it came to updating the JSON but we experienced faster SELECT query speeds which is a wonderful outcome. It was a great solution for our needs because we had quite a bit of unstructured data to manage and we didn't want to switch or add other databases quite yet!– Tyler BSep 18 at 23:18
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.