I have a general question about MySQL database table design. I have a table that contains ~ 650 thousand records, with approximately 100 thousand added per year. The data is requested quite frequently, 1.6 times per second on average.
It has the following structure right now
id port_id date product1_price product2_price product3_price
1 1 2012-01-01 100.00 200.00 155.00
2 2 2012-01-01 NULL 150.00 255.00
3 3 2012-01-01 300.00 NULL 355.00
4 1 2012-01-02 200.00 250.00 355.00
5 2 2012-01-02 400.00 230.00 255.00
Wouln't it be better to store the data in this manner?
id port_id date product price
1 1 2012-01-01 1 100
1 2 2012-01-01 1 200
1 3 2012-01-01 1 300
1 1 2012-01-02 1 240
Advantages of the alternative design:
- with the second design we don't have to store NULL values (if there is no such product in the port)
- we can add new products easily - comparing to the first design, where each new product requires a new column
Disadvantages of the alternative design:
- The number of records will increase from 650 000 to 650 000 * number_of_products minus all NULL records; that will be approximately 2.1 million records.
In both cases we have id column as PRIMARY_KEY and UNIQUE key on combination of port_id and date.
So the question is: which way to go? Disk space does not matter, the speed of the queries is the most important aspect.
Thank you for your attention.
Copyright Notice：Content Author:「user3382380」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/27185988/mysql-table-design-advice