Database IDs Have No Place in URIs (2008)

https://johntopley.com/2008/08/19/database-ids-have-no-place-in-uris/

by tosh • 7 years ago

71 167 7 years ago

Also, using a numerical auto-incrementing ID in the URL will expose how many users you have so far. Not a problem for Stack Overflow, but I’ve had clients (startups) who wanted to put IDs in the URL, when you sign up and your URL is /profile/432 it’s quite clear the product doesn’t have many users.

adjkant2• 7 years ago

An easy solution to that is simply not to start at zero/one and pick a random number somewhere around 5-6 digits. Can do that at the database level or programatically.

jstanley3• 7 years ago

It's still easy for anyone to work out how fast you're gaining users. Just sign up for an account every day and note how much higher the new id is.

treve• 7 years ago

A slightly more advanced version of this to XOR the number with some other number.

eli3• 7 years ago

At that point your requirements seem to require a number with no pattern, so it should probably just be a random unique number or string. But for a lot of use cases the ability to measure growth or enumerate URLs doesn't matter.

petepete• 7 years ago

    ALTER SEQUENCE users_id_seq RESTART WITH 54321;

"Wow, this service is probably mature, well-tested and reliable!"

lifthrasiir• 7 years ago

Note that there is a legitimate reason to set the initial sequence in this way: oftentimes the system works fine with 31/32-bit numbers but not with larger ones. Starting with the large sequence number (we have frequently used 10 billion) proactively eliminates this problem.

koolba• 7 years ago

And then come back a week later to see that 500001 => 500004...

ponytech• 7 years ago

This is why I always obfuscate IDs using Hashids: https://hashids.org

zepolen• 7 years ago

Hashids are slow though

branko_d4• 7 years ago

Just use GUIDs instead.

And if you want to keep using integer IDs internally, put the GUIDs into an alternate key.

varelaz• 7 years ago

GUIDs are really unreadable and long. You can read and remember some numeric ID, but GUID/UUIDs – totally impossible. I hated them after Microsoft COM.

thecatspaw4• 7 years ago

The issue I see with searching by slugs is thats its slower than searching by integer, and its more likely to be entered incorrectly by users when typing a url.

You also can not change the slugging algorithm if you want to keep bookmarks valid

adrianmsmith2• 7 years ago

I don’t agree it’s slower in a practical sense. If your service is backed by a relational database like PostgreSQL then you’d have an index on the column you’re looking up. So a request involves network round-trip latency, parsing the request and preparing the response, and searching for the object in the database using the index, most likely a tree with a depth of 3 or 4 levels. I don’t think the number of characters in the key, or the data type, will play a big role here. Happy to see evidence to the contrary though if I’m wrong!

The algorithm flexibility might not be a big problem either. The old slugs are stored in the database. Update the algorithm, new slugs adhere to the new algorithm, old ones unchanged, everything still works.

lxn• 7 years ago

1) Indexing text vs indexing integer it's very different when it comes to memory/space required by the database.

2) There is already an index on ids assuming they are used as primary keys, why adding another index (on text)?

3) To support changing slugs (titles) you will have to keep all the old slugs as well (to redirect them to their new versions). With ids you don't have to do this - ids have no reason to change.

In the end you might not feel the difference in execution time, but hardware requirements for servers...

I agree that they are annoying, but when the only reason against them is to improve URLs aesthetics (assuming they don't pose a security/information disclosure risk) - the trade of performance, hardware requirements and extra code needed is not worth it IMHO.

kozak• 7 years ago

That can be either good or bad depending on situation. There are plenty of cases where concise sequential identifiers are an advantage.

dexen5• 7 years ago

where concise sequential identifiers are an advantage

For example, certain kinds of attacks are helped with sequential IDs. Wasn't there an AT&T "hack" where the attackers iterated URLs over sequential account numbers to grab nearly all data? Would've been nigh impossible to perform with UUIDs as identifiers.

About the only use case I can think of where sequential IDs are helpful is using MAX(PRIMARY KEY) to roughly estimate total number of records. And that's only relevant where there's special cased support for MAX(PRIMARY KEY). Think MySQL, but not PostgreSQL. And that's also subject to the over-estimation due to holes left in the sequence, whether due to deleted records, or backed off transactions.

branko_d4• 7 years ago

Sequential integer IDs have some advantages over GUIDs at the database level. Integers are smaller (and cache friendlier!), while sequential values generally improve the fill factor of B-Trees (the DBMS can special-case the insertions that match the B-Tree key order, e.g. Oracle does a 90/10 index block split).

On the other hand, they can cause latch contention on INSERT-heavy tables (because of the competition for the last B-Tree leaf), though this is not common.

ehutch796• 7 years ago

It would have been nigh impossible if they actually had security and didn't return data to users that should be able to access it.

kevsim2• 7 years ago

Fully agree. For example, it's very handy that GitHub issues are auto incrementing integers starting at 1, so that I can type "Fixes #123" in a commit message instead of "Fixes #<some-uuid-or-something>".

Of course, that short numeric ID doesn't necessarily need to be part of the DB's primary key.

slig4• 7 years ago

Is there any best practice on implementing an ID field that starts at 1 for each repository (in GH's example)?

nly• 7 years ago

How would one go about implementing user scoped autoincrement fields efficiently in modern databases anyway? Triggers?

pointe• 7 years ago

You should not expose any internal identifier (numeric or otherwise) because it then becomes public & part of your API (even if undocumented).

ilikehurdles• 7 years ago

In one case the api I worked on used encrypted IDs on the way out to the client, and decrypted them on the way back to the db. Even when multiple clients referred to the same resource, they would see different public IDs, but any instance of the API could reach the underlying value from those public values.

Jwarder2• 7 years ago

One idea I like is to have a normal internal database ID, but also have a public UUID column to use for URLs, APIs and that sort of thing.

This way you can change the record's UUID at any time to display different data without having to worry about updating a bunch of internal foreign keys. You don't have to worry about a user making easy edits to the UUID in the URL to find nearby records (although you still need real protections if you need to restrict data by user).

mgamache• 7 years ago

I've used that in the past to retrofit an old database and move away from the id=123 in a URL to a UUID. You can keep all the old relationships triggers ect... Just add an index for the new col... UUIDs are not perfect but for most applications they are good enough (I think)

yrro• 7 years ago

There was a story posted recently about how the properties of UUIDs are not a good match for how databases index columns. It proposed the 'ulid' which helps in that regard.

wastedhours• 7 years ago

This is kind of how I've always built applications - the standard ID is viewable to admins, but every public facing utilisation is a random string (with a uniqueness validator).

Access to resources is either locked down by an authorisation policy, or sometimes you need open resources (like a public share link), in which case, security through obscurity (with perhaps a secondary string on the record acting as an additional key for non-logged in users).

_Codemonkeyism• 7 years ago

Funny me too, didn't think others would do the same.

aaronharnly3• 7 years ago

We do that. Treating the API key as distinct from DB id also helps with loosely coupled microservices, in which the key for a given item may have come from any of several source systems.

fastball2• 7 years ago

Why not just use the UUID as the ID?

jon-wood• 7 years ago

Using a UUID as your primary key can make certain optimisations much harder down the road, most notably sharding which tends to balance really nicely if you do it on an auto-incrementing numeric ID, and less so if you're using random strings because you'll end up with with some of them clustering.

They're also much harder to pass around in cases where you need to refer to a specific record, for example if you've got someone in tech support who needs help with a particular object its much easier to say "its record 18434" than it is to try and read out a UUID over the phone.

perlgeek• 7 years ago

This way you can change the record's UUID at any time to display different data without having to worry about updating a bunch of internal foreign keys.

That is true, but changing the UUID is also likely to break a lot of assumptions that your users make. If your user indexes your forum posts, for instance, and you change the UUIDs, they'll re-index posts with new UUIDs as new posts, and send end users to now invalid UUIDs until the old index entries are evicted.

aaronharnly3• 7 years ago

Yeah, I thought that line was weird. It's more like – you can change the record's internal foreign key without breaking your users! Once users have bookmarks, IDs more or less permanent.

athenot• 7 years ago

Why do you need an internal database ID?

I've gone the route of a random number only (similar to a UUID but encoded in a way similar to Youtube video IDs). I used to do the auto-incremented integer but once I got into database sharding, maintaining that global counter started getting a lot more complicated.

This lets me have inserts going to several databases without worrying about the counter. (Of course, as with any eventual consistency method, there's a mechanism to ensure no duplicates exist, however unlikely they are.)

aaronharnly3• 7 years ago

Those integer IDs can still be pretty darn efficient for a lot of use cases.

GauntletWizard• 7 years ago

This guy sounds like he has bad backups and bad database management. Nothing more. That "database ID" is probably an auto-incrementing primary key, and yes, moving it to another box, restoring from backups, and even migration to another server type should keep the same ID. If not, you've done it wrong.

In none of those situations will new entries necessarily follow the same pattern, i.e. there may be huge gaps or it might start filling in deleted questions so entries are "out of order", but those are separate problems and also remedied by using your schema correctly.

In short: this post is very misinformed and I hope that it's presented for that purpose and not taken as serious advice.

aiCeivi9• 7 years ago

this post is very misinformed.

This. The answer to 3 question in article is always "YES".

The slug itself can easily be automatically generated when a new question is saved. Then you can simply retrieve a question by its slug.

And that was just pure ignorance. What happens to slug when question(or product) is edited?

slig4• 7 years ago

For a content website such as SO, the biggest advantage of using /<id>/<slug>/ is being able to update the `slug` whenever the `title` of your content changes, and then it's very easy do a 301 redirect and you're good to go.

dqybh2• 7 years ago

If you're afraid of Google bombing, the redirect is not even necessary, just make sure the <link rel=canonical> tag contains the correct URL.

slig4• 7 years ago

That's a great suggestion, thanks!

eli3• 7 years ago

I prefer redirects because if someone edits a URL to look like /1234/offensive-phrase-here/ and shares the link I don't want it to appear that we choose that phrase as a slug.

dusted• 7 years ago

When talking about public pieces of information, being able to refer to them by their identity is useful and any hashing would be counterproductive (just wasting power), also, I'm sure any database can operate on unique, incrementing numbers more quickly than uuids. If we're talking about private information, it should be transferred in another way than the url in the first place, yes, the chance of "guessing" a hash is lower than an incrementing number, but there are other ways it could be intercepted, a screenshot, accidential copy/paste for example.

benkuhn• 7 years ago

This argument seems backwards.

As far as I know, no database has ever had any of the problems he mentions with IDs not round-tripping properly during backup/restore. (Please correct me if I'm wrong! But why do I think this is true? Because any database that doesn't roundtrip primary keys would break all foreign keys on backup/restore. Databases with broken backup/restore tend to either fix it real fast, or go away.)

On the contrary, it's much easier to keep IDs stable over time, than any other field. The problem with using a different field as an identifier (e.g. a URL component) is that almost any property with an objective meaning, might change over time or become non-unique. This is why it's a best practice not to use, e.g., user email as a primary key.

For instance, Stack Overflow allows users to edit the title of a post. Right now when that happens, the canonical URL changes (to include the new slug), but the old URL is easily 301'd to the new one because it includes the ID. If the numeric ID was not in the URL, you'd be stuck with either a misleading slug, or having to maintain a list of former slugs/redirects for each post. Plus, you'd have to hack your slug-generation algorithm to ensure the generated slugs to be unique, and the easiest way to do that is... by appending an incrementing ID to the end of the slug.

The point about avoiding cruft like .aspx in URLs is well taken, but unfortunately it's in direct tension with the point about keeping them stable given that "meaningful" non-cruft tends to change over time!

(And if you don't want someone to guess your metrics from IDs, you can use a random non-numeric ID instead!)

dexen5• 7 years ago

As far as I know, no database has ever had any of the problems he mentions with IDs not round-tripping properly during backup/restore.

Don't know of any such either. Typical SQLite, MySQL, or PostgerSQL dump/backup is just bunch of SQL statements that re-create the content, including PKs.

You can imagine a non-native (perhaps DB-agnostic?) DB dump format that would use generated IDs for PK, rather than actual PKs. Why would one do that, I don't know.

Another possible case is using file (or memory) offset as the PK. This gives certain nice properties - one less indirection level, and object identity being pointer identity. Perhaps there's a memory image-based LISP version out there that uses such. Perhaps there's a NoSQL engine like that somewhere.

Plus, you'd have to hack your slug-generation algorithm to ensure the generated slugs to be unique

...or you put the moderators to the task of ruthlessly stamping out duplicate questions. There was a popular website like that, oh wait, that's the Stack Overflow ;-)

[edit]

I've just remembered that PostgreSQL's ctid and SQLite's ROWID are physical identifiers (offset-like identifiers) of records. They are (to certain extent) exposed to userspace and could be misused as IDs in URLs. As physical identifiers (offset-like), they are naturally subject to change upon dump/restore, vacuum, other maintenance tasks, etc.. Clearly those should not be used as end-user visible IDs in URLs.

coldtea4• 7 years ago

As far as I know, no database has ever had any of the problems he mentions with IDs not round-tripping properly during backup/restore. (Please correct me if I'm wrong! But why do I think this is true? Because any database that doesn't roundtrip primary keys would break all foreign keys on backup/restore. Databases with broken backup/restore tend to either fix it real fast, or go away.)

You might not use foreign keys, but still have autoincremented primary key.

Database ids on the URL are also a security concern.

thecatspaw4• 7 years ago

Database ids on the URL are also a security concern.

They shouldnt be. Users should only be allowed access to their own records, even if they know the id's of records belonging to other users.

paulryanrogers• 7 years ago

Database ids on the URL are also a security concern.

More so than in mark up? If so why?

Should surrogate keys never be exposed to users in any form? Then what's the alternative? Nonces for everything, all the time?

SkyBelow2• 7 years ago

Isn't most of the security concern fixed by using guids instead of incrementing ints?

michaelt• 7 years ago

  IDs not round-tripping properly during backup/restore

Oracle has a "ROWID pseudocolumn" which uniquely identifies each row.

(I know this because I once had to fix a database table with no primary key, and some rows where every field was identical. The rowid lets you delete one out of two identical rows. So it's not an entirely useless feature)

If you had an inexperienced developer, and no-one reviewing their work, and they heard the rowid is the fastest way of looking up a row - faster even than the primary key - so they decided to use that in URLs, that would be tremendously inconvenient.

TeMPOraL3• 7 years ago

Few days ago I discovered that SQLite has ROWID too; the extra column gets created if your table doesn't have an INTEGER PRIMARY KEY and doesn't declare WITHOUT ROWID. Caveats apply, though. See: https://www.sqlite.org/rowidtable.html.

yetihehe2• 7 years ago

As far as I know, no database has ever had any of the problems he mentions with IDs not round-tripping properly during backup/restore.

Simple example - neo4j. Node id's are reused when deleted and you can't create node with id you want. Documentation mentions this. We've just used unique random strings as keys.

esonderegger• 7 years ago

I suppose it depends on the method used for backup and restore. If you replace the entire graph.db directory with one from a backup, ids are kept intact.

I was relying on this just yesterday. We use graphenedb for our production database and I needed to load a production backup locally for testing. It was really useful that I could query a node by id both locally and on prod and get the same result.

skybrian• 7 years ago

Old versions of MySQL had an edge case where, if you delete the last autoincremented id and restart the database, the ID was reused:

https://stackoverflow.com/questions/18692068/will-mysql-reus...

saltcured2• 7 years ago

PostgreSQL today is finally deprecating the OID feature they inherited from the original Postgres code. The OID used to be an implicit row ID that would have these problems.

The OID is generated from a sequence shared by all databases in the "cluster" i.e. the PostgreSQL server instance. So it is not only non-deterministic based on insert orders to your DB, but also is affected by inserts in other databases on the same server.

rlucas• 7 years ago

True, but for over a decade Postgres common wisdom has discouraged using the OID for anything in the app level.

As I recall, OIDs only ever came up in practice if you wound up wrapping the 32 bit uint.

cryptonector6• 7 years ago

Pick a primary key (i.e., set of columns) for a table. If you expose this outside the database in stable ways (e.g., in URIs), then do not permit changes to that table's rows' primary keys.

There's NO NEED for a primary key to be an INTEGER or any sort of ROWID.

I agree with TFA -if this is what it says- that one should not put INTEGER PRIMARY KEY values in URIs -- it's useless to users. Just make the "slug" a column, make a NOT NULL and UNIQUE, disallow changes, and use that. You don't have to make the slug a PRIMARY KEY -- NOT NULL and UNIQUE has the same semantics as PRIMARY KEY.

Surely TFA wouldn't argue to have no DB key at all in URIs, so I won't bother with that possibility. And yes, I should read TFA...

wvenable• 7 years ago

This seems like a lot of effort, with plenty of potentially fatal edge cases, for extremely little gain.

A small number in a URL isn't going to confuse anyone.

derefr2• 7 years ago

What do you do when literally every "natural" column of your table is mutable, but you need the row's identity to be stable?

Well, you need what is called a https://en.wikipedia.org/wiki/Surrogate_key.

Surrogate keys need to be made up from whole cloth, apart from the natural data (because if they are derived from any of the natural data, they'll become incorrect when the data changes.) And what's easy to make up from whole cloth? Sequential integers. (Or random numbers, which—if you want to guarantee no collisions across a distributed DB cluster—gives you ≥128-bit random numbers, i.e. UUIDv4s.)

jabart• 7 years ago

That is terrible advice.

1. Numeric based Primary Key Lookups are some of the fastest operations on modern databases. 2. "Slugs", are a set of strings, which occupy more memory, space, now you have to come up with some way to make them unique. If you are building a CMS, sure, SEO is important. If you are building a line of business application, no. 3. Just checked, Google and Facebook expose numeric based identifiers. My Facebook Business ID is numeric, my Google Analytics ID is numeric with a prefix on it. 4. There are plenty of libraries out there that will take a numeric value, convert it to a small alphanumeric value with a check digit. 5. No end user cares about URLs (except for SEO). Your business user is not going to navigate your application by going to myapp/settings, they are going to click on the gear icon.

megous• 7 years ago

it's useless to users

It's pretty useful to me when scraping/syncing data from a website. I'm also a user.

speedplane• 7 years ago

it's much easier to keep IDs stable over time ... [otherwise] you'd have to hack your slug-generation algorithm to ensure the generated slugs to be unique[.] ... [A]voiding cruft ... [is] in direct tension with the point about keeping them stable given that "meaningful" non-cruft tends to change over time!

Many applications have meaningful unique identifiers, creating a unique slug is not always a "hack". Any app that displays events that occurred in the past often have unique identifiers. Examples include software logs, email, government records, financial transactions, i.e., anything that displays past events.

These records are inherently "write-once", and the primary key can be something more meaningful than an ID. These may be just a subset of apps out there, but in these cases there is a strong argument for removing any ID in a URL.

fjni• 7 years ago

Genuine question: What's the proposal for items in an API which have no concept of a "slug"? We could generate a separate mapping of "public ids" to the database internal ones, but I don't even see how that would solve any of the issues here.

theoh• 7 years ago

I'm not a database expert, but concepts like surrogate and natural keys come to mind. A unique slug is a natural key, but for most, if not all, items, you can come up with a natural key by combining a few bits of data (I've seen name * date of birth used by doctors in general practice, for example). Often there's a pre-existing unique code external to the app which can be used, like a driver's licence number or something like that. That removes the need for a surrogate key in the database.

The tricky case is something like email drafts: it's possible for a user to create and then save a draft with no addressee, subject or content. In that case there is little alternative but to make the system use a surrogate key (a database internal key) because the other possible unique identifying values (e.g. time of creation of the draft) are sort of unsatisfactory, because they don't really have a strong, meaningful connection with the content of the item.

trpc• 7 years ago

I like the way medium.com deals with IDs, 10 random hex chars (i.e. > 10^12) put at the end of the slug are more than enough for most use cases that can be addressed via a url and still clean for search engines and humans.

But also the way letterboxd and also quora deal with URLs is admirable but it needs special care (at least in the case of quora)

localhostdotdev• 7 years ago

pretty cool how the urls stayed the same:

http://stackoverflow.com/questions/13204/why-doesnt-my-cron-... (still works)

http://stackoverflow.com/questions/21064/massive-reputation-... ("this question was removed from stack overflow for reasons of moderation", still available: https://i.imgur.com/Csy94IL.png)

jayrwren• 7 years ago

Pretty sure the last 10yrs of stackoverflow proves that this is not a concern.

I'd file this under: things to not worry about.

geezerjay3• 7 years ago

Sequential numeric ids leak information regarding your operations.

https://en.m.wikipedia.org/wiki/German_tank_problem

wutman• 7 years ago

This is exactly right, but in my opinion usually not important. If Joe Public, or a competitor, or whoever knows how many posts you have, users you have, administrators you have, etc how much does that actually affect your operation? I guess they can better compete against you in some small way with that information, but in my opinion it's almost never worth the effort to obfuscate. It's like optimizing for a problem you don't have.

coldtea4• 7 years ago

of Atwood's calibre

What was Atwood's calibre at that point? (pre StackOverflow)

boobsbr• 7 years ago

Coding Horror began in 2004, that's where I knew him from.

fabian2k• 7 years ago

That article seems to immediately contradict itself by saying that the most important aspect of URIs is that they never change, and then suggests using the slug derived from the title as unique identifier. Titles change all the time on Stack Overflow, this would immediately break URLs.

bazzargh• 7 years ago

The title and slug are related but not tied together. It's quite common to visit slug-based sites and find the slug is a relic of the original title. In addition, slugs have to be unique when used this way, but titles don't. eg a title of Database IDs Have No Place in URIs (2008) may appear again next year, but the url would need to point to a different conversation; your CMS would suggest or generate the slug based on the original title.

eli3• 7 years ago

The title of a news article sometimes changes because of new reporting that makes the original title inaccurate. Leaving the old title as the slug could cause confusion. Think about a breaking news event where details are very sketchy at first but the article is updated as more becomes known.

ricardobeat• 7 years ago

And then the question gets updated and you either have a broken URI, or a slug that doesn’t match the content.

I’ve never heard of IDs being something mutable. When would that ever happen?

btbuildem• 7 years ago

One reason would be that the slugs are not guaranteed to be unique - they're based on user-entered post titles.

geophile• 7 years ago

I don't get this argument at all.

If the ID comes from something like a SERIAL column, then yes, you can port those easily. I think every database system has something like SERIAL, as well as the ability to control an initial value, taking care of portability concerns.

SERIAL has other problems, namely predictability, so another possibility is UUIDs. And those should be pretty portable too. I know DBMS UUID implementations are different from OS implementations, but the risk of collision should still be vanishingly small.

And if you don't use IDs, then what's the choice? If the URI contains an article name then what about article names being changed? What about duplicate article names?

steego• 7 years ago

I can't believe people have been giving this post this much credit in the first place. All of his contrived examples are solved with trivial methods and it just sounds like he doesn't know how to migrate data from one database system to another.

LoSboccacc• 7 years ago

this seems backward, as far as the pk remain meaningless they're easier to port around, not harder. it's once you give them a meaning then trouble starts (for example you can't change the slug down the road if the title changes)

and with integer keys the worst that can happen is that the sequence is not in the backup so after a restore you need to have the p sequence restart from the previous max id + 1; meanwhile with slug built off user entered data a change in collation can really screw up your day, unless you want to mangle all texts into basic ascii, and SO doesn't https://stackoverflow.com/questions/41102371/sql-doesnt-diff... (see the ü in the link more than the question itself)

cryptonector6• 7 years ago

Better blog title: "Do not use INTEGER PRIMARY KEYs in URIs" or "Do not use ROWIDs in URIs".

xg15• 7 years ago

What does he propose I do if two questions map to the same slug or if - beware - someone wants to edit their question?

ehutch796• 7 years ago

I don't understand why people are saying this is a security issue.

If user a has private document 1234 at /doc/1234 and user b has private document 1235 at /doc/1235

when user a goes to /dec/1235 IT SHOULD NOT RETURN THAT DOCUMENT.

Who in their right minds considers obscurity to be a replacement for authorization?

If a random anonymous individual can access private data by hitting a url, the problem is not the id used in the url.

ehutch796• 7 years ago

Also, what about when your users are all generating multiple entries with the same name/slug? thing-name-1532 isn't any easier than things/1532/thing-name

I have no reason to enforce uniqueness of the name of things, but I MUST do this because some jerk said so in 2008?

airstrike2• 7 years ago

when user a goes to /dec/1235 IT SHOULD NOT RETURN THAT DOCUMENT.

As a matter of fact, it should return 404 NOT FOUND for both users A and B

jolmg2• 7 years ago

ehutch79 said

user b has private document 1235 at /doc/1235

So, why do you say it should return a 404 NOT FOUND for user B?

newscracker2• 7 years ago

There’s some time for the OP to edit and fix that typo. But yeah, I noticed that at first glance too.

sprt• 7 years ago

It's at least a user enumeration issue

ehutch796• 7 years ago

Why? what's the actual issue? If you have access to it, wouldn't you know the id anyways?

ehutch796• 7 years ago

the /dec/1235 bit was a typo.

I mean really?

airstrike2• 7 years ago

Laugh a little, maybe

JMTQp8lwXL• 7 years ago

UUID's are a good solution to this. Less and less do I see sites using an increment primary key as an id. I'd like to think that using a UUID is more scalable too -- no need to keep track of what the last ID was.

_fzslm• 7 years ago

Looks like Hacker News does this too:

https://news.ycombinator.com/item?id=1

jolmg2• 7 years ago

If you have to move the site to a different box, can you guarantee that those database IDs will remain the same?

If you don't, then your foreign keys will be broken.

If you have to restore the site’s data from a backup, can you guarantee that those database IDs will remain the same?

Ditto. If they're not, then that backup seems broken.

If you have to switch to a different database server (say from Microsoft SQL Server to Oracle), can you guarantee that those database IDs will remain the same?

Ditto.

I've only had one situation where I had to change the database ids, including making sure to change all foreign keys accordingly in all tables, and that was when I was migrating multiple company-hosted instances of a webapp they had in their intranets to a unified internet-hosted server. I was merging multiple databases into one so modifying ids was necessary so they wouldn't clash.

At least in my case, I don't think any of those companies' employees would have had the expectation for their intranet URLs to work. I mean, even the host/domain part of the URI changed. Why would they have the expectation for the path part to still work?

madrox• 7 years ago

I get the vibe a lot of these critiques are rooted in academic understanding of the issues rather than real world practice. In practice, these are all problems that can be addressed...some rather trivially.

The biggest real world concern I've ever had with embedding incremental IDs into URLs is that incremental IDs can leak information, like how many records you have and how quickly that's growing. Depending on your business, you may want to obfuscate that.

3xblah• 7 years ago

"URIs in Stack Overflow look like this:

http://stackoverflow.com/questions/13204/why-doesnt-my-cron-...

Article gives only cons. What are the pros?

https://stackoverflow.com/q/13204

Shorter, easier for users to work with. Could be even shorter with a domain hack. Does Stack Overflow have one?

https://en.wikipedia.org/wiki/Domain_hack

http://xona.com/domainhacks/s.cgi?q=stackoverflow.com

JeremyBanks• 7 years ago

They have s.tk but it's mostly used for internal/intranet links: https://meta.stackexchange.com/q/109795/134300

cmiles74• 7 years ago

I worked with a customer that was concerned about the "security" of database IDs in the URL. I though their solution was novel: they chose a random long integer whenever a new row was created. It's fast like an integer and random like a UUID, the best of both worlds!

einrealist• 7 years ago

"URIs should be meaningful, not cluttered with meaningless information."

Actually, URIs are opaque. The parts of an URI are implementation details - even the protocol, domain and port. To the user, changing database IDs, that happen to be part of the URI, is no different to changing the domain, port number or the protocol.

Whenever I get into a discussion about "URI design", I state two rules:

1) Care about the URI design as much as you want. But never expect from someone else - especially the client - to make sense of that design.

2) When necessary, handle URI changes in a way that does not break the client (redirects are your friend).

Btw. For this reason, browsers tend to remove the address bar from the UI (as much as possible).

spartas• 7 years ago

It's a good thing™ Stack Overflow doesn't have those IDs in the URI anymore.

dr01d• 7 years ago

Uuids

michrassena• 7 years ago

Say I'm convinced that IDs could possibly leak information, but UUIDs are problematic (e.g. cost of enforcing uniqueness grows with each added row). Would encrypting the ID with some symmetric key plus a salt be sufficient? The client sees a number where the ID can be recovered on the server side, but which the benefits of having an easily generatable unique identifier while making it challenging to recover the actual ID).

Geee• 7 years ago

This is bullshit. It's absolutely necessary for the user to be able to refer to the resource by an ID of some sort. A slug is not an ID.

jasonhansel• 7 years ago

If you want to estimate the total # of items in a DB based on auto-increment ID numbers, you can use: https://en.m.wikipedia.org/wiki/German_tank_problem

(Apparently this was used by militaries in WW2, to estimate the total number of tanks from the serial numbers printed on them.)

Database IDs Have No Place in URIs (2008)

Related Stories