Multiple tables or 1 big table

Question

Hi,

I'm going to log the consumption (water, electricity, gas, ect) for plenty of devices (take 400 devices which log every hour and we need to keep the data at least 2 or 3 years) .

Now i was wondering if i can better put them in 1 table with the following colums:
[Type][Device][Value][Date]
Type: Water, Electricity or Gas
Device: Which device we are logging
Value: The actual value
Date: date when the record was logged

Or can i better create a table for every device with only 2 column (Value and Date).

Because when i need the data for reporting, i never going to need all the devices (report we want to make are 32 devices).
I can make the Type and Device column indexed, but performance wise isn't it better to read only the tables i need?
Or is it more difficult to read multiple tables at the same time?

Thanks at advance!
Kind regards,
Ward

Answer 1

400 devices x 24 h x 365 days x 3 years = 10.5 million records, that is very less data; go the way with one table.
I have tables with billions of records and no performance issues.

One table per device has the disadvantage, that you always have to add a new table if you get a new device, you have to change reports, and so on.

Answer 2

@Olaf Helper thanks for the reply.

Will go for the one table then.

Answer 3

Hi,

From the perspective of the size, complexity and use(report) of the data that needs to be stored, or the performance of the query , I think you only need to create a total table include all devices.
It is easier to query the data of 32 devices from one table than to combine results from 32 independent tables.