0

Assume the following schema:

ID  | TENANT | TENANT_CUSTOM_ID
346 | 101    | 1  
347 | 102    | 1  
348 | 101    | 2  
349 | 101    | 3  
350 | 101    | 4  
351 | 102    | 2  
352 | 103    | 1

What is another name (or the proper classification) for TENANT_CUSTOM_ID? Perhaps something along the lines of a surrogate key? The purpose of this key is to make each tenant have their own numbering system instead of knowing that their records are mixed with all other tenants on the same tables.

0
1

A natural key (AKA business key or domain key) is a key that is used to identify information in the business domain.

A surrogate is a key that is "unseen", internal to the database, meaningless and not used in the business domain.

The distinction that really matters is whether the attribute in question is exposed to users / consumers and therefore serves some function and acquires meaning as part of a real world process.

It seems that there is already too much loose terminology and folklore associated with keys. I don't think it would be helpful to have another term for the kind of key you are referring to here.

4
  • 1
    In the age of public APIs, what does a surrogate key mean with that definition? Jun 2 '17 at 22:19
  • 1
    In a public API your business/domain keys are the keys used in the business domain. I.e. the public API. If you didn't intend a key to be used in that way then what would be the point of making it public?
    – nvogel
    Jun 2 '17 at 22:30
  • I would intend for them to be used that way, my question is if I'm opening up my business to everyone then what would a key look like that wasn't in the API? I think you're placing a lot of weight on propriety and secrecy, so I just want confirmation that I understand your definition. If everything is available or if I let others use my surrogate key, even if only in an api, you believe that makes it a natural key because it's in the "business domain"? Jun 2 '17 at 22:36
  • 1
    Exactly. A non-meaningful key existing only in the database and not exposed via an API is a surrogate. It isn't about secrecy as such. Surrogates are used because they don't have any presence in the business domain and therefore are entirely within the control of developers. Developers are free to refactor and modify those keys without any external impact. By contrast, business keys ought to be changed/redesigned only by business users, or only with their consent. If you "refactored" an invoice number without consultation then the accounts department are probably not going to be happy.
    – nvogel
    Jun 3 '17 at 7:30
0

It's definitely not a surrogate key; It's a natural. A surrogate key is something you add to the data. If it's provided by the client and specific to their data, it's a natural key for your purposes.

It's like a UPC or the like. If none of that makes sense, for your purposes it needn't be there. Architecturally, you'll likely never use it and you'll never need it. You also aren't free to change it. That's all indicators that it's natural. However, I wouldn't call it "TENANT_CUSTOM_ID". I'd go for something more regular, like "SERIAL_NO." That's normally how you link out to a tenant/vendor/supplier database.

Update, if you're generating it.

If you're generating it, it's a surrogate key. A second surrogate key. But what's the point? You should never generate two surrogate keys. If you just need to hide your own primary key, consider

  • generating a single UUID instead, UUIDs are slightly bigger but it will save you the trigger.
  • hashids

If you're not deleting the rows, considering using a window-function with row_number() OVER (PARTITION BY tenant ORDER BY id).

If you absolutely must have the tenant-specific id, I would further suggest dropping your regular id and going with a composite key tenant, tenant_custom_id. You may as well make it crucial to the system.

I also wouldn't call it _custom_id if it's not custom and you're generating it.

Lastly, I wouldn't bug about using the same pkey on multi-tenant. It's quite normal.

From Wikipedia, (take with a grain of salt)

A surrogate key (or synthetic key, entity identifier, system-generated key, database sequence number, factless key, technical key, or arbitrary unique identifier[citation needed]) in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural (or business) key which is derived from application data.

A natural key (also known as business key[1]) is a type of unique key, found in relational model database design, that is formed of attributes that already exist in the real world. It is used in business-related columns.[2] In other words, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called domain key.[3]

15
  • 1
    An invoice number (the number used by the account payable department and by external suppliers) is not a surrogate key no matter how it is generated. Generator functions both inside or outside the database are commonly used for natural keys as well as surrogates.
    – nvogel
    Jun 2 '17 at 20:50
  • 2
    All information in a database consists of symbols generated by humans or machines. In that sense all attributes are "surrogates" for the real things they identify. I agree that "natural" is a very loose term and I much prefer the term business key or domain key. However, the relational model's definition of a surrogate originated with E.F.Codd and relates specifically to whether the key is exposed to users and processes outside the database. If a key is exposed then it loses any "meaningless", purely technical character it might have had and is therefore a business key, not surrogate.
    – nvogel
    Jun 2 '17 at 21:09
  • 1
    Codd's definition is in his paper "Extending the database relational model to capture more meaning", which I quoted in the post you refer to. From a designer's perspective there is a big practical difference between a key intended for external business use and one that isn't exposed at all. An invoice number gets recorded outside the system and therefore cannot be changed without consequences because it is "meaningful" outside the database. A key that is not exposed doesn't suffer that problem - it is de facto a "meaningless" key. The method used to generate it is unimportant in either case.
    – nvogel
    Jun 2 '17 at 22:06
  • 1
    Similarly, is an invoice number any more "natural" if it is keyed-in than if it is generated in the database? As long as your audience understands you it doesn't especially matter what you choose to call it. I would still expect that the distinction between A) a key your customers see and use and care about and B) one they don't, is usually pretty important. The public API is your contract with paying customers. This is the distinction that Codd was making when he introduced the idea of a surrogate.
    – nvogel
    Jun 2 '17 at 22:28
  • 1
    @EvanCarroll, I'll object to the statement "Surrogate keys don't become natural keys". A surrogate key in one system, often becomes a natural key in another system.
    – Lennart
    Jun 3 '17 at 18:11

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