How to design table for dynamic number of columns?

Question

Requirement: We have to capture the data for a day in particular time intervals(time interval is constant for a set of data). The time interval can range from 5 minutes to 2 hours . So the number of data points for a day can vary from 12 to 288. How should we design our table to accommodate this variation.

Can we go up with adding 288 columns in the table ? If the time interval is 5 minutes then all 288 columns will be occupied. If its 2 hours than only the 1st 12 columns will be occupied. and so on.

Which DBMS are you using? In Postgres you can use the hstore datatype to overcome the limitations of the EAV pattern. — user330315, Commented Jul 18, 2013 at 12:02
thanks for your reply...currently we are using postgres..but the db can be moved to oracle or mysql at later point.. — adihere, Commented Jul 18, 2013 at 15:34

Scott Allen · Accepted Answer · 2013-07-18 11:59:23Z

2

You do not want 288 column table.

It's difficult to design the table with no concept of the other tables around it, but basicaly - have a column for time, and add the other times. If you have a time related to an object, ad a foreign key to the object it relates to. If you want intervals rather than the time you can have an 'interval' column, which just notes with interval it is part of.

    "TimeOfEvent"
    PK TimeofEventId
    FK EventId
    Interval
    Timestamp

answered Jul 18, 2013 at 11:59

Scott Allen

5334 silver badges13 bronze badges

1

So according to this approach we will have 288rows entry for a day. Since we need to manipulate around 2months data in a go wont it degrade the performance both on the database side and coding side where we are using the data to get some output
– adihere
Commented Jul 18, 2013 at 16:04
I understand what you are saying, and this is my opinion: Based on your description, this table will have between 12 and 288 rows per day. This is a lot but at least if there are only 12 intervals in the day - there are only 12 rows of data to scan. If you were to create 288 columns; Your query have to scan between 0 to 176 null values to retrieve the data it needs.
– Scott Allen
Commented Jul 18, 2013 at 17:31
Also, as your datasets have consistent time intervals; you could get around my point of scanning 176 null vales by defining which columns to select. Regardless of this, I still thinks it's a bad idea. Your SELECT statements could be very long and difficult to manage if you have to go through each scenario of 5 minute, 10 minute, 20 minute, ext and write these. I don't believe using a TimeOfEvent table would cause a detrimental effect on performance over selecting each column (though I couldn't assure this) and you could set variables in a single query to achieve different results.
– Scott Allen
Commented Jul 18, 2013 at 17:43
It's interesting how difficult it is to find any links on this.. then when you do find one it brings you right back to stackoverflow. Couldn't find anything overly helpful.
– Scott Allen
Commented Jul 18, 2013 at 18:13
thanks for your help. we will go with the row concpet instead of the column thing..
– adihere
Commented Jul 19, 2013 at 12:41

Add a comment |

Collectives™ on Stack Overflow

How to design table for dynamic number of columns?

1 Answer 1

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related