5

I have a table called devices. Most of the devices that will get stored in this table can be uniquely identified by their serial number and part number. But there are some device types that do not have serial number and part number assigned to them. Instead they can be uniquely identified by another field (internal id).

Should I create a surrogate key for this table or should I create a composite primary key (serial number, part number, internal id) and insert default values to the serial number and part number columns when they are not supplied? The device types that do not have part number and serial number now, will have the numbers assigned to them in the future releases (may be 5 years from now). Should I create a surrogate key or a composite key in this scenario? Or using the three unique attributes, should I create a hash in the program and use that as a surrogate key for the tables?

  • 1
    This question has asome good details about surrogate performance vs natural keys. – Max Vernon Aug 26 '17 at 16:33
3

Use the surrogate key as the primary key for the moment. When natural keys become available, make them non-nullable unique constraints.

By the YAGNI principle, you should only code for "real-life" current requirements - a primary key that may (or may not) arrive in 5 years is not worth considering now!

Surrogate keys are very widely used in practice, much to the horror of purists: the rest of us just use them! See this Stack Overflow answer (as well as the rest of the thread).

2

I think we should start out clarifying that we are talking about Primary Keys, not alternate keys, right?. And since we are talking about Primary Keys, it is best that they do not change over time. And also best to be as small as feasible, especially if there will be any FKs pointing to this PK.

A major reason to avoid natural keys (outside of not having it in all cases yet — and 5 years is a long time to wait, especially for something that might not even happen) is the reliability factor: can you trust the source of the data? Is it machine generated or entered by a person? Humans entering data can, and often do, make mistakes. And even if the data is machine generated, it is still possible that a bug in the import process does something that mangles the values somehow. For this reason alone I tend to prefer surrogate keys. Sure, there are people who will complain that surrogate values are "meaningless", but we aren't talking about looking at a logical data model on paper or on screen. A value that is reliable and efficient will win over "meaningful" every time. A 30 byte (or whatever) key copied into several tables (and then into several more indexes) that will most likely be compared (even for JOINs) with a case-insensitive Collation makes no sense when one can choose a 4 byte INT that is a simple binary comparison.

Your "natural" key column should be indexed and is an alternate key. This is what the app will use to look up the record to get the surrogate key that will be used in all subsequent JOINs and queries. This column can change — datatype, length, values, etc — over time as much as the business requests, all without requiring you to tell the folks above you that the requested change will take an extra 1 - 6 months (depending on size of the system and scope of the change) because it affects so many tables, which means more time to plan, more time to develop the changes, more time needed for testing, and more risk that something won't be caught in testing and will cause 1 or more customers to log support requests which upsets customers and is a drag on support staff.

A composite key, while not inherently wrong, doesn't seem to add anything here. And in fact, it would seem to counter the uniqueness of the existing serial numbers for those device types that have them.

I would also not recommend using "the three unique attributes [to] create a hash in the program and use that as a surrogate key". Again, that seems like a value that can change if any of those three "unique attributes" ever change. It will also be larger than ideal for use as a PK, not to mention the non-sequential nature of a hash that will leave you in the same boat as those who started out thinking that GUIDs were a great choice for PKs ;-).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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