1

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.

2
  • Do you have a preliminary design that can be enhanced? And what database do you want to use for storage?
    – Eniola
    Oct 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 Oct 8, 2020 at 13:27

3 Answers 3

2

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
  • 1
    Or better yet - use PostgreSQL! :-)
    – Vérace
    Oct 8, 2020 at 20:09
0

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!

2
  • Is there no downside to it, such as lower execution speed for queries? Sep 13, 2023 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 B
    Sep 18, 2023 at 23:18
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.