2

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 DATETIME2.

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?

Improve this question
10
  • 5
    Why an IDENTITY column? If all you need to be able to do is differentiate between system 1 and system 2, why not just append a 1 to every row from system 1, and a 2 to every row from system 2, when merging the data together? Not sure how an IDENTITY column will help avoid duplicates. One of the problems with IDENTITY in a distributed system is that you have collisions by definition. And I don't see why you need to differentiate every row in system 1 from every row in system 2.
    – Aaron Bertrand
    Commented Mar 15, 2012 at 0:27
  • Having an identity column ANYWHERE in the primary key will guarantee that that it will be unique. If the data will mainly be selected by the datetime, I would say that your indexing strategy to make the clustered index (datetime, identity) is sound.
    – Chris Smith
    Commented Mar 15, 2012 at 1:58
  • @ChrisSmith how does IDENTITY help when the two tables need to be merged? While it makes collisions much less likely, it doesn't guarantee against them, unless the ranges of identity values on the two servers are mutually exclusive.
    – Aaron Bertrand
    Commented Mar 15, 2012 at 4:02
  • 1
    And datetime2 + system is not unique enough? Can events on any one system happen at the exact same instance in time (e.g. 2012-03-15 12:21:32.7612521)?
    – Aaron Bertrand
    Commented Mar 15, 2012 at 16:21
  • 2
    @AaronBertrand datetime2 alone definitely is not unique enough on any decent box. I recall collisions on Db2 a decade ago, with microsecond accuracy.
    – A-K
    Commented Mar 18, 2012 at 19:24

1 Answer 1

Reset to default
2

Don't confuse the primary key with the key of the clustered index. Though they are often the same, they are independent. You just need your primary key to uniquely identify a record in your table across all the databases that have a copy of it.

Since recalling this data needs to be performance driven, I know right off the bat I want a clustered index on DATETIME2.

I agree with this choice, since you will primarily be writing, reading, and merging data by this column. Be sure to pick the right precision for your DATETIME2.

As for the primary key, I suggest making it (system, id), where system is a unique system identifier and id is an IDENTITY value. A CHAR(1) or TINYINT will suffice for the system identifier if you have only a handful of systems.

Don't bother managing the ranges of the IDENTITY values across the different systems. They can overlap as they please since the system identifier will distinguish records from different systems that share the same id.

Improve this answer
5
  • I like this suggestion. I am working on trying to get a some better fidelity on the size of the system identifier. I have some assumptions, but I need to confirm.
    – abaderts
    Commented Mar 19, 2012 at 17:47
  • The system identifier will need to be varchar(10). Would you still recommend (system, id) as the PK or would I be better off with a GUID?
    – abaderts
    Commented Mar 20, 2012 at 18:43
  • @abaderts - Do you need to know what system the data came from when it is merged? If not, you could use just a GUID. How many sensor systems do you expect to have in total? In the interest of keeping your PK as small as reasonably possible, consider also aliasing that VARCHAR(10) system name to a TINYINTor SMALLINT in a lookup table that is maintained on the system where the data is merged.
    – Nick Chammas
    Commented Mar 20, 2012 at 19:01
  • Yep...the customer needs to know what system the data came from when merged. Currently this first pass will record data from up to 10 sensors (including derivatives of that data) with a possibility of more in the future. More systems may be added also. I like the idea of a small lookup table. However my concern would be with a new system added. If I took this approach, I would need to make sure I provision for the possibility if the customer forgot record the system id in the lookup table before deploying.
    – abaderts
    Commented Mar 20, 2012 at 20:07
  • @abaderts - Then I advise you to stick with (system, id). Set system to have a distinct DEFAULT value for each different system, and make sure no SQL is explicitly writing to that column. It's no big deal if the customer forgets to map the system number to a human-friendly string. You can always add that in at your leisure. The core of your data logic should rely on the surrogate value, and the lookup table should just be used for reporting purposes.
    – Nick Chammas
    Commented Mar 20, 2012 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.