4

This question already has an answer here:

I'm building an app with a Client table that lists clients and it's PK by an auto-incrementing ID field.

I need an immutable unique identifier for each client that will be used in business logic. For example client will upload a single file to an AWS S3 bucket, I want to use this ID as part of the file naming convention.

I'm hesitant about using the surrogate key as that field because I feel it would be coupling my business logic to my model layer.

The alternative would be adding a separate ID field, that's not used for foreign key relations and only used in business logic, but then I'd have to generate that myself everytime a new client is created.

Is the alternative worth doing in the long run, or am I overthinking it?

marked as duplicate by gnat, Community Nov 5 '17 at 15:52

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    As a minor terminological nit, your concern is with surrogate keys, not primary keys in general. – Derek Elkins Nov 5 '17 at 5:31
4

This is ok as long as

  • using the key externally does not impose any relevant constraints on the keys

  • the only kind of BL usage is still "identification", and the fact one uses them does not impose any additional requirements on the key of changing them later.

  • externalizing the key does not expose any secret business information which must be kept private (like how many sales per year a company does)

For example, the constraint that now all characters of the key must be valid for files is probably acceptable in most real world cases and does not really couple the BL layer to a critical implementation detail. However, if one gets restrictions on the automatic ID generation because of some kind of naming collisions in the BL, then this is probably not acceptable any more.

I assume in your case these things don't apply, but it is fine to invest a thought in the specific case.

  • 1
    The number of clients is likely partially leaked by the auto-increment ID field and may be business sensitive. This could be solved by using UUID surrogate keys. (While it is likely irrelevant in this case, there are also some scaling issues with auto-increment fields that UUIDs also resolve, though with their own costs.) – Derek Elkins Nov 5 '17 at 5:56
  • @DerekElkins: exactly my point. Your comment shows how business requirements might start producing requirements on the key, in which case one might start thinking about using a separate field for the UUIDs, like the OP suggested. – Doc Brown Nov 5 '17 at 6:04
  • I think a UUID field is what I'm going to go with. It is cleaner, and the duplicate question @Doc Brown posted seemed to raise some extra points such as data migration which might interfere with using the surrogate in BL – Jad S Nov 5 '17 at 6:05
  • @JadS: it is not "cleaner" in general, it is just sometimes a solution which might fit better to the requirements (or not). And as I wrote, I don't agree with that other answer 100% (see the discussion with Robert Harvey below that answer). Look at your specific case, think about it, and don't make a "cargo cult" decision. – Doc Brown Nov 5 '17 at 6:11
  • I went through that discussion, some good points raised on both sides. – Jad S Nov 5 '17 at 6:22

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