I am currently developing a new database schema with an opportunity to do things right. The purpose of the development effort is to collect data (events) from various sensors that will be recorded with a time/date stamp.
Currently there will be two disconnected databases at two different locations capturing data that may or may not occur on the same day. On a monthly basis, the drives will be pulled and sent to the customer's main office to ingest the data and merge into one database.
Once the data is available, the customer will replay the data for analysis as if they were actually there when it was captured.
Since recalling this data needs to be performance driven, I know right off the bat I want a clustered index on
This leads me to the problem of designing the primary key to account for merging.
Would a composite primary key of
DATETIME2 and an
IDENTITY column be unique enough to avoid any collisions when the data is merged? For example, some data may be captured at a 50hz to 100hz rate.
Or is that too much of a wild card and it would be best to use a GUID as the PK? If that is the case, how should I handle a clustered index for performance (for example) where a child table would have a PK of two columns: a GUID from the parent table and a 3 char column "product" identifier?
Would it be best to add a
DATETIME2 column to become the clustered index or am I making a mountain out of a mole hill?