22

To some of my tables I want to add "second_primary_key" which will be uuid or some random long key. I need it because for some tables I don't want to expose integers to my web application. That is, on a page "/invoices" I have a list of invoices and a link to "/invoices/:id" where :id is an integer. I don't want a user to know how many invoices in my system there, hence instead of "/invoices/123" I want to use its "second_primary_key" so that the url will be "/invoices/N_8Zk241vNa"

The same goes for other tables where I want to hide real id.

I wonder, is this a common practice? What's the best way to implement this?

And what is this technique called after all, so that I do a search on it?

13
  • 20
    Why not get rid of the integer altogether?
    – larsbe
    Aug 22 '17 at 8:14
  • 4
    You can define as many unique keys/indexes as you like on a table. Aug 22 '17 at 10:02
  • 2
    Perhaps you should call it a secondary candidate key. "Primary" suggests only one. Aug 22 '17 at 10:08
  • 4
    "Second primary" is an oxymoron. You have a primary key, and you can have secondary keys.
    – OrangeDog
    Aug 22 '17 at 11:40
  • 7
    @RobbieDee there are valid reasons for not having a database fully normalized. And having a candidate or secondary key is not exactly duplicating data.
    – Machado
    Aug 22 '17 at 12:18

11 Answers 11

0

You could add a UUID column but you really don't need to (and shouldn't). This is a presentation layer concern. You wouldn't dream of say, storing a currency value as $1,999 as well as 1999.

You just want some way of obscuring the value on the fly for the application. You could either do this in the application itself or as a database view.

Since we're only talking about a single value, maybe look at 2 way encryption such as AES or similar - the more lightweight the better.

Hashing could be another possibility - it depends on whether you want to get the invoice number back, since hashing is one way.

48

Having an "alternative primary key" is a well know concept in relational database modeling, it is called "alternate key", or sometimes also "secondary key". The set of "potential primary keys" is called "candidate keys". See https://beginnersbook.com/2015/04/alternate-key-in-dbms/

How you implement this is completely up to you, especially if you want to hide the total number of records. There is no "best way", you should check your requirements like allowed or useful character set, maximum length, if you want the IDs to be case-sensitive or not, if you want them to be readable on a printed invoice, if someone must be able to respell them on the phone without errors, and so on.

17
  • 11
    I've also seen the terms Natural key vs.Surrogate Key used to describe this scenario.
    – DanK
    Aug 22 '17 at 12:28
  • 2
    @Dari: you asked "what is this technique called" - in bold letters. And if AES decryption - maybe on the fly - produces keys of the kind you are looking for, use it, that does not contradict my answer.
    – Doc Brown
    Aug 22 '17 at 13:12
  • 1
    @Dari Because it adds a completely unnecessary overhead to your app
    – Lamak
    Aug 22 '17 at 16:19
  • 1
    @RobbieDee We already got it that you don't like alternate keys, but that doesn't mean they are useless. I like the guid approach because it simplify a lot of problems.
    – T. Sar
    Aug 23 '17 at 14:58
  • 1
    @RobbieDee We don't use SQL Server. We use MySql. And it happens because someone will create something on Prod, let's say with ID 1234. On Dev, naturally, we make many more entities than we do on prod. 1234 was taken long ago by some throwaway entity for testing. When we have to test an entity from prod, we have to migrate it back to Dev - and its primary key is already in use. The migration is much easier if references to that entity are guid based. But hibernate works much better with a primary key being int or long, so we keep that. My devs are not lazy or ignorant - they're seasoned.
    – corsiKa
    Aug 23 '17 at 15:04
9

Most invoices have an invoice number, that by most accounting rules need to be sequential or an accountant might not sign off on the year results or the IRS (or similar in your country) might wish to do a full audit on your tabs.

A user could deduce from the invoice number how many customers you've served, or how long it was before you changed the numbering strategy on invoices.

How many invoices are stored in the database is no measure of the grand total of your invoices. There are other means of finding that out, including requesting your year reports from the Chamber of Commerce.

I would, however, lock the invoice behind a user login screen, so not everyone can request it. Then on the user login, they can use an ajax methodology to request their outstanding invoices, etc. This secures your data, hides the URL by ajax (nobody can usually be bothered to look at the details of how the ajax request is built up), and you control how the data is displayed and offered.

4
  • 7
    A common strategy used in banking (with check numbers) is to not start the incremental count at 1 but rather some larger number for this exact reason.
    – DanK
    Aug 22 '17 at 12:45
  • I think that's why the id is to be an additional primary key, not a replacement of the old primary key.
    – Alexander
    Aug 22 '17 at 12:56
  • 1
    I would not call it a primary key. I'd go for a slug, an UUID as name, but in essence it's just another indexed field in the table. Quote id, invoice number, whatever. It's a field, but not a primary key. A primary key needs to be unique, and can be used internally for relational mapping. If the field in indexed it can be searched quickly by a where query. userXveryY.where('invoice_number','foobarbaz10').get(); Aug 22 '17 at 13:03
  • 1
    You are answering a technical question with an argument that it' s not needed because of USA peculiarities (required sequential invoice numbers, reports at the Chamber of Commerce). IMO this doesn't answer the question well. Aug 23 '17 at 12:22
7

You may be able to use hashids for this, it's designed to solve exactly this scenario.

It will encode your database ID into a short hash (similar to a YouTube video's URL), and it won't require you to add any secondary keys to your table.

2
  • 2
    Name is somewhat misleading, as it is not hash, but reversible function. But it seems to be the perfect solution to the problem. Aug 23 '17 at 13:07
  • 2
    @CrazyYoghurt True... they addressed the reason for naming it as they did here: hashids.org/#why-hashids
    – Eric King
    Aug 23 '17 at 18:05
3

You can create another unique key, but you should not. Not for the reason given. There simpler ways of hiding the table sizes.

Storing N_8Zk241vNa costs 12 bytes per row in the table and even more in the index. That's pretty wasteful for what you need.

Encrypting the integer id costs you no space and close to nothing in the run time. How you do it depends on your programming language and/or your database.

Note that with AES you get a 128 bit integer, which means 22 characters in base64, probably more than you want. A cipher with a block size of 64 like DES or 3DES gives you 11 characters, just like you want.

Use different keys for different tables.

If all you need is hiding the tables sizes, you may use a common sequence for all tables. Note that it may be bottleneck if there are frequent insertions in many of your tables. With something like Hibernate and a Hi-Lo algorithm, this problem disappears.

7
  • Exactly - storing this value just to hide another is just wrong.
    – Robbie Dee
    Aug 22 '17 at 12:25
  • This may work in this scenario as an invoice ID isn't really confidential but as a general rule using confidential IDs as the relational structure in a database will cause a royal headache if you need to mask data at some point in the future. Better to treat them as an attribute.
    – DanK
    Aug 22 '17 at 12:43
  • how can I apply aes here?
    – Dari
    Aug 22 '17 at 12:45
  • @Dari How can you apply AES to anything? Without knowing your language, nobody can tell. Usually, AES works with a byte[], you can write your id in four or eight bytes, add a unique table number and encrypt (the input must be exactly 16 bytes). If there are modes to choose from, ECB is right.
    – maaartinus
    Aug 22 '17 at 14:02
  • @DanK What? Are you claiming AES is insecure? Without knowing the key, there's nothing the attacker could do any better than for a stored attribute. Nothing. +++ I guess, I'm not understanding your comment.
    – maaartinus
    Aug 22 '17 at 14:05
0

IMHO creating two different primary keys is not possible. Of course you can put that uuid in a DB to have it as "alias" for current primary key. You can put an index above that column with unique constraint, but primary key is (from its essence) single one within a single table. There can be composite primary key, but that is not what you are looking for.

So I suggest putting it there, but having it only with index. You can create handling component for querying data by PK as well as other unique column. When handling request for "/invoices/..." just check the parameter - if it is integer, search the ID, otherwise search uuid. Or you can have the uuid search as a fallback when ID search did not found anything.

And about generating some "random" uuids: Why not something like "take ID, add CONSTANT, convert to hexadecimal". Iniqueness of ID will provide uniqueness of uuid, hexadecimal number is harder to read for normal mortals + adding constant will avoid having uuid like 00000001.

6
  • 1
    "Why not something like "take ID, add CONSTANT, convert to hexadecimal" - because that is pretty easy to figure out - give me an URL and I'll have a look at all other invoices in the system. IMO there is no problem that this actually solves, just ones it potentially creates.
    – CompuChip
    Aug 22 '17 at 13:15
  • "When handling request for "/invoices/..." just check the parameter - if it is integer, search the ID, otherwise search uuid" The whole point (as I understand the question) is to prevent someone searching by ID (/invoices/123, /invoices/124,...) so you would only search by UUID from the URL.
    – TripeHound
    Aug 22 '17 at 14:06
  • Also, not all hexadecimal numbers contain letters. It would be impossible to always distinguish between your underlying integers and your generated hex numbers.
    – TRiG
    Aug 22 '17 at 16:32
  • @CompuChip as I expect, you are interested in computers :-) so you recognize the hex number for first sight. But the Q was written in the manner not to show the invoice number directly to let others know how many invoices there are. When I show some hex number to my wife, mother, neighbour... they won't know what that "strange text" is. If there will be notice about security issue according to invoice numbers within the Q, then I would suggest some complex hashing method for that purpose.
    – Jarda
    Aug 23 '17 at 6:11
  • @TripeHound he still might be able to search by ID internally or within some access-restricted entry point...
    – Jarda
    Aug 23 '17 at 6:14
0

If both keys are pointing to the same fact, and they would never collide. Why not derive the other key from the original one using some scalar function that would create custom hash code of your original key.

alternatively, you can create an annex mapping table, that would store both versions of the key. this table will act as a dictionary to lookup the secondary key.

According to my understanding, keys are implicit indices, and the more you add indices, the slower inserts will be.

1
  • +1 Yep, adding what is potentially a large string column with an index certainly isn't the value free operation others suggest. Storage overhead aside, as indexes get added, insertion speed starts to degrade.
    – Robbie Dee
    Aug 23 '17 at 7:33
0

Another approach for your particular use case is that instead of modifying the database and the application, you can just create a custom route to the invoices so the /invoices/:f(id) where f(id) is some function of the id.

The custom route is responsible to map a request to the correct action server-side.

0
0

It is a totally acceptable practice, also called 'Alternate Key' (AK). Basically the AK is another unique index or unique constraint.

You can even create foreign key constraints based on your AK.

A possible use case is like what you explained: you have a clustered PK on an ever increasing identity number, but you don't want this number to be displayed or used as search criteria, because it can simply be guessed. So in addition you have a random unique identifier or reference number as an AK, and that is the ID you present to the user

0

There are several kinds of keys/indexes. A primary key is a special unique index, and as the answers say you can certainly create another unique key. And I agree that it is best to not expose your database internals unless there is a very good reason.

Since the question is in the context of invoices and numbers, it might be worthwhile to research how the accounting industry expects invoice numbers to look: http://smallbusiness.chron.com/assign-invoice-numbers-52422.html

It may seem messy to have an internal id that is a primary key and another unique field with the application/customer visible invoice number. But it isn't so unclean when, say a year down the road, the customer wants to adopt a new invoice numbering scheme. In that case you'd not disturbing the internal id and its relations in other tables to renumber the entire ball of wax. You'd keep your internal id as is and re-number the non-internal invoice number.

Ideally you try hard not to tie tables together on keys/foreign keys that are likely to change, and keep your internal tables and relations transparent to the app layer.

0

Go for it.

This is not dissimilar from a "slug" field that blog articles and the like often have -- a unique way to refer to the database record separate from the primary key, fit for use in a URL. I've never heard anyone argue against those.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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