Best way to store real world "events" in a DB?

I'm building a system which will collect data about an industrial process, which is externally controlled. Those datas will be used to build usage statistics for various components of the system.

Simplified example: there's a heater that is turned on and off, and I get notified when it happens. I need to log this, and based on these data be able to answer questions like "How long has the heater been on last month?"

What I 开发者_运维百科came up with is to create a table in which I insert a line each time a state change happens, include a timestamp.

However, it seems to me that it will require quite a lot of after-processing, eg to answer the example question above. I see no way to extract this kind of answer with just SQL.

Question: is there a better suited, more effective "storage pattern" that what I describe here?

Thanks.

You could store the time the heater was on, rather than the discrete on/off events. Use time_on and time_off columns to track when the heater was turned on and off respectively, and then subtract time_on from time_off to get the duration.

When the heater is turned on:

insert into heater_usage (time_on, time_off) values (now(), null);

When the heater is turned off:

update heater_usage set time_off = now() where time_off is null;

Use unique constraints to insure no two rows can have null for time_off, as a basic check to make sure you don't leave "dangling" records with no time_off if your script isn't invoked properly. You could check for those when the heater is turned on, and remove them.

To sum the total time on:

select sum(time_off - time_on) from heater_usage;

I dont think you have provided enough information to be able to propose a design.

I am sure that you are storing more than just one event type; is it a few, or is it a very large amount.

how different is the data that needs to be stored for each event type?

how often will this system need to be changed? will you have to edit or add event types regularly or rarely?

is this a system that has to be flexible to the type of data that an event produces?

that said, you effectively have two main types of design possibilities:

create a unique table for every event type that explicitly captures data for the event type OR create a limited number of tables that can store data for many event types which have a column containing xml, or serialised data of some form.

the first is less flexible, the second requires more post processing.