Daniel Kimani Daniel Kimani - 4 months ago 6
SQL Question

Best approach in Designing Rainfall_data Table in MYSQL Database

I am designing a database that is intended to store daily rainfall data from various stations. i have to store daily records regardless whether it rained or not i.e if there were rains i indicated the millimeters reading from the rain - gauges and if were no rains i just indicate zero.

The approaches i have in mind is that i have the following fields in my "rainfall_data" table:

Option 1 :

id| station | year | month | day1 | day2 | day1 | day3| day4 | day5 ....| day31

Option 2

id| station | year | month | day |

I do have a feeling that Option1 is better since i will have less rows in my "rainfall_data" table

Looking for advise. Thanks


Approach 1 is actually the less favorable option.


1) it's expensive to add or remove columns (requires ALTER TABLE which locks the entire table and has to restructure all existing data), while it is easy to add or remove rows

2) inserting data requires locking the existing row, while inserting a new one leaves existing data accesible (this however highly depends on the database engine)

3) a big number of rows is not a problem for a DBMS, they're designed to handle that. However a big number of columsn, especially if a lot of them are empty for most of the time, increases database size without any reason (an empty value in a column needs as much space as any non-empty one, with the exception of varchar columns)