CODEWITHSUNDEEP
sqldatabase-designmariadbdatabase-performancedatabase-normalization
jackfr0st
jackfr0st

Reputation: 551

What table structure to go for if there are two objects of same type but of different nature?

Given that there are two kinds of products X and Y. X has A, B and C as the primary key whereas Y has A and D as it's primary key. Should I put them in the same table ? Why should I and if I should not, then why is that ?

I have currently put them in two separate tables but some colleagues are suggesting that they belong in the same table. My question is should I consider putting them in the same table or continue with different tables?

Below I have given example tables for the above case.

CREATE TABLE `product_type_b` (
    `PRODUCT_CODE` VARCHAR(50) NOT NULL,
    `COMPONENT_CODE` VARCHAR(50) NOT NULL,
    `GROUP_INDICATOR` VARCHAR(50) NULL DEFAULT NULL,
    `RECORD_TIMESTAMP` DATE NULL DEFAULT NULL,
    PRIMARY KEY (`PRODUCT_CODE`, `COMPONENT_CODE`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `product_type_a` (
    `PRODUCT_CODE` VARCHAR(50) NOT NULL,
    `CHOICE_OF_COVER` VARCHAR(50) NOT NULL,
    `PLAN_TYPE` VARCHAR(50) NOT NULL,
    `RECORD_TIMESTAMP` DATE NULL DEFAULT NULL,
    `PRODUCT_TENURE` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`PRODUCT_CODE`, `CHOICE_OF_COVER`, `PLAN_TYPE`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

As you can see there are certain fields that are not common to both tables but are part of the primary key. There are also some other fields which are not common to both tables.

Here is the bigger picture of the system in consideration.

I want to have good read and write speeds without compromising much. So should I go ahead with this design ? If not, what design should be implemented ?

Upvotes: 0

Views: 603

Answers (2)

Serg
Serg

Reputation: 22811

For a trading system and taking into account max 5 product types and very limited number of attributes I'd prefer a single table for all products with a surrogate PK. Think about references to products from trading transactions, this is the biggest part of the total DB content in a long run.

A metadata table describing every product-specific attribute and its mapping to the general table column would help to build UI and backend/frontend communications.

Search indexes would reflect most popular user seraches depending on product type.

Upvotes: 1

PeterHe
PeterHe

Reputation: 2766

This is typical Category/SubCategory model issue. There are a few options:

  1. Put everything in one table, which will have some columns NULLable because different subtypes do not have the same attributes;

  2. One parent table for all the common attributes, and also with the column of the type indication column. Then each sub type has its own table just for the columns the Subtype has.

  3. Each subtype has its own table, including all the common columns of all the sub type.

(1) is good if the sub type is very limited;
(3) is suitable if the variations of the sub types are very limited.

The advantage of (2). is it is easy to return all the records with the common columns. And if an artificial key (like auto-increment id) is used, it ensures all records, regards less the sub type, has unique id.

In your case, no artificial PK is used, I think your choice is not bad.

Upvotes: 1

Related Questions