Split product table into several tables


#1

Hi guys,
I wonder how you would handle product data. Everything in one product table or splitting into several tables?

Some products have like weight, height, length and other products have totally different data like voltage, fuel type.

So either have a huge product table with all kinds of columns were only some are populated depending on product type or just the data that are mandatory for all products like name price and so on and then all extra columns stored in separate joined tables.

Please let me know how you would do this.


#2

Your issue is not related to AdonisJs. It is a matter of modeling.
Do not expect an accurate and serious answer to your problem here because you have to take time to design an entity relationship model (ERM) that will help you to translate your business model into a relational model (MySQL tables).

To design your ERM, you will need to follow a modeling method such as MERISE or any other method you prefer.
With regards.


#3

My opinion about it(this requires a greater effort) two proposals.

Option One

  • Using only one table with this columns

CREATE TABLE products(
     id INT PRIMARY KEY AUTO_INCREMENT,
     nameProduct VARCHAR(20) NOT NULL,
     category_id INT NOT NULL,
     weightProduct DECIMAL(5, 2) NULL,
     heightProduct DECIMAL(5, 2) NULL,
     voltageProduct DECIMAL(5, 2) NULL,
    buyed_at DATETIME NOT NULL DEFAULT NOW()
);

So, here some clarifications

  • Only one table to concentrate all records
  • Some columns that are not common for every product, must to be NULL so with this attribute you can store products with different characteristics
  • Use a FK like user_id to identify every record

Option Two

You can store data in a JSON format to simplify process, so your table will looks like this


CREATE TABLE products(
     id INT PRIMARY KEY AUTO_INCREMENT,
     nameProduct VARCHAR(20) NOT NULL,
     category_id INT NOT NULL,
     attributes JSON NOT NULL,
     buyed_at DATETIME NOT NULL DEFAULT NOW()
    ...... more columns
);

At this point you need to be clear which RDBMS are you using

For example if you choose to store your data in a JSON format on mariaDB Server, then check my tutorials about how to do it, right here ->

(mariadb json functions)[https://github.com/ShadowPaz/mariadb-server-cheat-sheet/blob/master/JSON%20Functions%20extended%20version.md]