I am trying to work out the best database design for the following application.
In a factory we have about 500 machines/sensors that send back data every minute when in operation.
Each record received has
Event Type - High / Low / Normal / Startup / Shutdown etc.
Various small data fields
For an average 8 hour day there would be 240,000 records and we keep records for many years.
All of the queries will have a data range as part of the search. We will be querying things like -
Records between Date1 and Date2 WHERE MachineID = X
Records between Date1 and Date2 WHERE EventType = Low
Records between Date1 and Date2 WHERE MachineID = X and EventType = 5
Latest record WHERE MachineID = X and EventType = 1
Should each machineID have its own table?
Should by primary key be a composite of DateTime MachineID and EventType? or should it be a 'surrogate' key?
What sort of index should I create?