18

My workplace's database has a pattern that I've not seen before. Every column that is intended to be a key, whether primary or foreign, ends in _SK. This is shorthand for "surrogate key". It appears to be an informal way to tell the developer that said column is safe to use in joins and won't have any type mismatches or unexpected behavior. For example, our table of dates has many columns that represent the date. DATE_PLAIN is the typical SQL DATE variable that shows data ISO style (e.g. 2022-10-30), DATE_VENDOR puts the date in the style that our vendor uses (e.g. 44300... their epoch is weird), and DATE_SK is always an INT that uses the familiar 20221030 format. By reading these column names, the pattern immediately tells the developer that DATE_SK is the one that you want for joins. Someone who uses either of the other two options in joins will run in to type mismatches and trouble (I learned that the hard way, e.g. our vendor inconsistently stored their dates as INT and DECIMAL).

This strikes me as a remarkably good idea, which raises the question of why I've not seen it before. Is it a known anti-pattern?

17
  • 19
    UPDATE sales SET DATE_SK = 20221029 WHERE DATE_PLAIN = '2022-10-30' Ooops. Commented May 30, 2022 at 18:36
  • 48
    The weird thing is having the same date in multiple formats. A single date format should be sufficient.
    – JacquesB
    Commented May 30, 2022 at 20:00
  • 61
    @J.Mini it’s a violation of “single source of truth”. Store data. Not presentations. Commented May 31, 2022 at 1:00
  • 41
    If you want two date columns but one source of truth, some dbms offer computed columns, or use a View. Storing it twice is a bad idea.
    – John Wu
    Commented May 31, 2022 at 3:23
  • 10
    One antipattern I see here is using integers for dates as if they were strings. This allows for invalid dates to be encoded. I once had to deal with data like this where you would find Feb 29th for non-leap years. It also requires more storage than a date type. One source of confusion may be that date types aren't stored as ISO strings or any other human readable format.
    – JimmyJames
    Commented May 31, 2022 at 16:19

3 Answers 3

54

The use of surrogate keys is by itself not an anti pattern. It is a way to create a stable primary key for an entity, that will never change and not depend on any application data.

But there are three anti-patterns in the practice you describe:

  • Using a suffix _SK to identify potential surrogate keys is a variant of the Hungarian notation, with all its drawbacks
  • Calling surrogate key DATE_SK that bears value such as 20220531 is misleading since it is in reality a natural key with a special encoding, so just the contrary of a surrogate key that should be completely unrelated to meaning to the data it refers to.
  • Keeping several DATEs columns in a same table with different encodings to refer to the same date seems to be a denormalization, with the risk of inconsistencies.
11
  • 29
    Though I agree to point 2 and 3, I don't see "_SK" as a form of Hungarian notation - at least, it does not have "all its drawbacks". Have a look at the drawbacks section in the Wikipedia article you cited, most of them refer to type redundancy of "Systems Hungarian" in statically typed languages, which does not apply here. In fact, going through the list of those 12 disadvantages listed there 1-by-1, I did not find one which clearly applies here.
    – Doc Brown
    Commented May 31, 2022 at 8:21
  • 4
    @DocBrown Thanks for the opportunity to clarify. As said, it is a variant, so the drawbacks have to be transposed. I think for example that "The Hungarian notation is redundant when type-checking is done by the compiler." stays relevant, since type management is done by the SQL engine. Moreover it is also redundant with foreign key constraint and primary key definition if the design is based on sk. Also "It may lead to inconsistency when code is modified or ported. If a variable's type is changed" is very relevant in an SQL context.
    – Christophe
    Commented May 31, 2022 at 13:11
  • 1
    Last but not least, it can be embarrassing when class members in some OO code accessing the DB suddenly get such suffixes whereas the naming conventions might prohibit Hungarian notation. Btw, this phenomenon might propagate and amplify drawbacks.
    – Christophe
    Commented May 31, 2022 at 13:22
  • Well, I have used (surrogate) keys of the consistent form "TablenameID", and it leads to datamodels for which several things can be generated. Try this without a suffix.
    – Doc Brown
    Commented May 31, 2022 at 13:31
  • 4
    also: dates are not surrogate keys to begin with. Commented Jun 1, 2022 at 13:01
29

There is nothing wrong in using surrogate keys as primary keys in a consistent way throughout a whole database schema, for each table, with a defined type and a column name suffix like "_SK". There is also nothing wrong in using corresponding foreign keys, with corresponding column names to the primary key columns whenever possible (hence also getting the suffix "_SK"). I have seen this working well in practice for larger data models (with different suffixes, but that does not matter).

However, IMHO it is not a good idea to abuse a standard term like "surrogate key" with a specific meaning for a key which does not fit to that definition - that will sooner or later create confusion. The whole point of a surrogate key is that it is completely artifical with zero domain logic encoded, for example a GUID or some raw ID or ordinal number. Your example for DATE_SK which encodes a date is clearly an example of not being a surrogate key, it is quite the opposite of it, a natural or domain key.

So I would recommend to either call those kind of keys differently, or use real surrogate keys instead.

8
  • 1
    There's a noteworthy omission in this answer. You say that I'm using the wrong name for this pattern and that you've seen the same pattern with different naming conventions. What are those naming conventions and the correct name for this pattern? At minimum, that would be great info for the interested reader to stick in a search engine.
    – J. Mini
    Commented May 30, 2022 at 21:39
  • 8
    @J.Mini: sorry if I wasn't clear: the exact suffix for the surrogate key columns does not matter. If you would use a a different one like "id" or "key", that would not solve the issue. But when you talk of "surrogate keys": that's a technical term with a specific meaning which is contrary to the content of the date_sk. If you would name it just "date_id" that would only partially solve the issue. Better stick to your convention and use real surrogate keys.
    – Doc Brown
    Commented May 31, 2022 at 5:34
  • 2
    @J.Mini What he's saying is that the system labels them "Surrogate keys" when they're actually simply fields that are well enough sanitized to use as keys. Commented May 31, 2022 at 6:20
  • 1
    @LorenPechtel: right, and I guess interpreting the suffix "SK" as an abbreviation for "sanitized key" instead of "surrogate key" could probably work as well.
    – Doc Brown
    Commented May 31, 2022 at 6:39
  • @J.Mini This "pattern" is encoding the fact that there is an index in the name of the field. It doesn't really have anything to do with keys. Of itself it doesn't sound like a good idea to me, nor (as others have mentioned) storing the same data in multiple formats. At minimum there had better be a table constraint ensuring the date columns are all for the same date.
    – OrangeDog
    Commented May 31, 2022 at 11:35
9

First of all, whoever came up with this idea seems to misunderstood what a surrogate key is. A surrogate key is a form of primary key. There are two competing ideas among database administrators about how primary keys should be structured. One philosophy is that of "natural keys". This philosophy says that when your data already has an unique identifier, use it. The other philosophy is that of "surrogate keys". Adherents to this philosophy believe that natural keys are often either not as unique and immutable as you assume them to be, or much longer than required. So you should use an additional ID column as primary key for each table which contains auto-generated values which are guaranteed to be unique, also known as a surrogate key.

Which of those philosophies is correct is besides the point. But fact is that a column which isn't the primary key isn't a surrogate key... at least for this table. It might be the surrogate key of another table. Then it would be a foreign key referencing a surrogate key. But it would not be a surrogate key in itself.

However, a consistent naming convention for columns which contain keys from other tables is actually a good idea. Especially if that column contains a true surrogate key, because a true surrogate key would not allow you to guess what table it could refer to just based on context. I have seen this naming convention before. But usually the name for it is ID. For example, when you have a table customer, that table has a primary key ID containing a surrogate key. When you then have a table order, that table would have a column customer_ID which references customer.ID.

And here we see another advantage of surrogate keys: Because of the synthetic nature of surrogate keys, nobody would ever get the idea to store them in different formats. When your customer.ID is, say, a 8 character hexadecimal string (because someone thought that was a good idea at some time), then it would make zero sense to encode customer_ID as an integer. You just use the format of the column you reference.

What is a bad idea, though, is to have multiple fields which contain the exact same data in different formats. Not only does it increase the size of the data, it also allows inconsistencies. If you do need multiple representations of the same data for convenience, then create a VIEW for the table which projects the one date column onto multiple view columns showing that date in different formats. And when that conversion in real-time eats up too much performance in your particular use-case, then materialize that view.

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.