4 ms·

The most important property of surrogates, which I never see mentioned, is that two tuples, anywhere in the database, have the same surrogate key if and only if

The most important property of surrogates, which I never see mentioned, is that two tuples, anywhere in the database, have the same surrogate key if and only if they are (believed) to refer to the same real-world entity. This is a crucial difference wrt auto-incrementing or randomly generated values that are independent in different relations (which is a common practice). Among the rest, with surrogates defined as above you may freely join relations on surrogate keys being sure that you are joining related data. In his landmark 1979’s paper, Codd defined surrogates and pretty clearly, and that part of his work was based on an earlier paper entitled On Entities and Relations (I don’t recall the authors right now), so this is not exactly news. Unfortunately, current DBMSs provide little to no support for properly implemented surrogates.

dragonwriter 9y ago
> Unfortunately, current DBMSs provide little to no support for properly implemented surrogates. UUIDs instead of per-table autoincrement keys seem to be a solution which covers the part of the problem that involves DB support as opposed to data model design, and several DBs have adequate support for UUIDs.
- ProblemFactory 9y ago
  UUIDs (and other mostly random IDs) also have the important advantage that they can be generated offline - in mobile apps, client-side javascript or elsewhere. With autoincrement keys, you need to either contact the database server for each data object you create, or implement complex logic to replace temporary IDs with database IDs later.
  - dullgiulio 9y ago
    Nope, you can't. You still have to check for uniqueness on the server side before committing them, so you are back from the start. Always keep in mind that anything coming from the client cannot be trusted.
    - ProblemFactory 9y ago
      You can't blindly overwrite existing data based on the UUID, sure. But I find that the security checks against "stealing UUIDs" are much simpler to implement than a system of composite primary keys (deviceid+autoincrement or userid+autoincrement) or some sort of temporary primary keys. If you see suspicious UUIDs, you can just discard the data. Having the client generate the "final" IDs for objects is particularly convenient when the client can produce a complex graph of objects offline, for example an order + order items + comments + more. If using temporary IDs until first contact with server, all these links would have to be patched up when the objects get real server-side-assigned IDs.
jeltz 9y ago
I would argue that the support is there. You can either use random UUIDs or a global shared sequence for this. Nothing requires you to have a sequence per table other than some synthactic sugar in the SQL standard. PostgreSQL also has its own OIDs, but I think that is more of a legacy from before they implemented sequences.

dragonwriter 9y ago

> Unfortunately, current DBMSs provide little to no support for properly implemented surrogates. UUIDs instead of per-table autoincrement keys seem to be a solution which covers the part of the problem that involves DB support as opposed to data model design, and several DBs have adequate support for UUIDs.

ProblemFactory 9y ago
UUIDs (and other mostly random IDs) also have the important advantage that they can be generated offline - in mobile apps, client-side javascript or elsewhere. With autoincrement keys, you need to either contact the database server for each data object you create, or implement complex logic to replace temporary IDs with database IDs later.
- dullgiulio 9y ago
  Nope, you can't. You still have to check for uniqueness on the server side before committing them, so you are back from the start. Always keep in mind that anything coming from the client cannot be trusted.
  - ProblemFactory 9y ago
    You can't blindly overwrite existing data based on the UUID, sure. But I find that the security checks against "stealing UUIDs" are much simpler to implement than a system of composite primary keys (deviceid+autoincrement or userid+autoincrement) or some sort of temporary primary keys. If you see suspicious UUIDs, you can just discard the data. Having the client generate the "final" IDs for objects is particularly convenient when the client can produce a complex graph of objects offline, for example an order + order items + comments + more. If using temporary IDs until first contact with server, all these links would have to be patched up when the objects get real server-side-assigned IDs.

jeltz 9y ago

I would argue that the support is there. You can either use random UUIDs or a global shared sequence for this. Nothing requires you to have a sequence per table other than some synthactic sugar in the SQL standard. PostgreSQL also has its own OIDs, but I think that is more of a legacy from before they implemented sequences.