Re:Database IDs Have No Place in URIs (2008)
by dexen on Friday May 10, 2019 @08:46AM
(#19877193)
>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.
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.