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? – Eniola Oct 8 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 Pereira Oct 8 at 13:27

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

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

  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 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:

  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)

  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.

| improve this answer | |
  • 1
    Or better yet - use PostgreSQL! :-) – Vérace Oct 8 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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