I have one single table with 25 columns and I want to split it into 3 or 4 tables with a smaller column count. Below is a simplified example as a reference:
This is the big table
| supplier | brand | sku | product_class | size | sales | trans_id |
|---|---|---|---|---|---|---|
| supplier_1 | brand_1 | sku_1 | class_1 | 3.5 | 100 | 1 |
| supplier_1 | brand_2 | sku_2 | class_1 | 3.5 | 200 | 2 |
I need something like this:
product_info
| sku | product_class | size | trans_id |
|---|---|---|---|
| sku_1 | class_1 | 3.5 | 1 |
| sku_2 | class_1 | 3.5 | 2 |
product_sales
| supplier | brand | sales | trans_id |
|---|---|---|---|
| supplier_1 | brand_1 | 100 | 1 |
| supplier_1 | brand_2 | 200 | 2 |
In this case, trans_id from the product_info table would be my PRIMARY KEY.
CodePudding user response：
You can split tables like Products, Brands, Carts. Then connect them using foreign keys.
Products table can have the column company_id that refers to its company.
The carts table holds users' purchases information and has columns such as product_id and user_id.
It's also better to have a CartProducts table that one shipment may contain multiple products.
CodePudding user response：
Maybe use views (i.e. logical tables) for the purpose. Lightweight and efficient. Here is an illustration:
create or replace view product_info as
select trans_id, sku, product_class, size
from the_big_table;
create or replace view product_sales as
select trans_id, supplier, brand, sales
from the_big_table;
-- more view definitions here
Joining these views would be trivial and with no overhead:
select ...
from product_info join product_sales using (trans_id);