Database design strategy for large column tables

Question

I am working on a project where I have to deal with large column table storing meter readings for power stations. Presently date is being stored in a table as below.

Table A

Date, Block No, Station 1, Station 2, ....... , Station N (N can go upto 650)
2013-05-21, 10, 23, -45,........ , 57

Now there is another Table B which has fields derived from Table A.

Table B

Date, Block No, F1, F2, ....... , FX 
2013-05-21, 10, 23, -45,........ , 57

Here Table B fields are derived as below

• F1= Station 1 + Station 3,
• Fx = Station p + station r + Station w

Now I want to change this approach of having a field for each station and derived field. I want to make tables as below.

MyTable A

Date, Block, Station_Name, Reading
2013-05-21, 10, Station 1, 23
2013-05-21, 10, Station 2, -45
.
.
.
2013-05-21, 10, Station N, 57

My questions are:

• Is my proposed normalized design will have processing impacts?
• Generally how such tables should be designed, what are best practices?
• Will SQL be more complex in my approach for updating derived field of Table B compared to earlier one?

Answer 1

Will my proposed normalized design have processing impacts?

Yes. Inserts and updates will be a little faster, while selects will be a little slower. However, a normalized database design is what relational database engines were designed to process.

Generally how should such tables should be designed, what are best practices?

Database normalization is always appropriate, whether you use a relational database or some NoSQL solution.

Once upon a time, in the ancient days of relational databases, there was a tradeoff between normalization and performance. Really good database analysts knew where to make those tradeoffs.

Today, relational database engines are capable of running fully normalized databases.

Will the SQL be more complex in my approach for updating derived field of Table B compared to earlier one?

It will be different. I can't say it will be more complex. Instead of retrieving one row per block, you'll retrieve 650 rows per block.

If you always have 650 rows per block, you don't gain much by normalizing. If there are a variable number of rows, up to 650, you'll gain a little in processing by retrieving just what you have.