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.
UPDATE sales SET DATE_SK = 20221029 WHERE DATE_PLAIN = '2022-10-30'
Ooops.