What are the disadvantages in using IDs suffixed by a table identifying number?

Question

I'm playing with:

DROP TABLE users CASCADE;
CREATE SEQUENCE users_id_seq MINVALUE 100;
CREATE TABLE users (
    id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('users_id_seq') * 100 + 1,
    ...
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

DROP TABLE txns CASCADE;
CREATE SEQUENCE txns_seq MINVALUE 100;
CREATE TABLE txns (
    id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('txns_seq') * 100 + 2,
    ...
);
ALTER SEQUENCE txns_seq OWNED BY txns.id;
...

To my mind, this has the following benefits:

Each id is unique across the whole database, removing wrong-table errors.
No id has a value of less than 10,000, removing confusion with most iteration counters.
The containing table is easily learned from the last two digits of any id.

Disadvantages:

We're wasting 99% of ids.

What other disadvantages haven't I considered?

None of what you think is a "benefit" is really a benefit. Especially the "the table is learned from the ID" quite moot. A primary key (and a generated one especially) should never carry any "meta information". The only job of a generated primary key is to be unique for that table. And the "removing confusion" part seems extremely questionable to me. Where would that "confusion" arise? When I debug my program? Makes zero sense to me. — , Feb 20 '21 at 08:09
@a_horse_with_no_name I have experience with untangling some horrors (not of my making). Aiding debugging and defending against errors, in depth, is second nature. Perhaps Postgres, with enforced correctness of foreign keys, may not need the measures which seem sane when working with ancient MySQL databases where many foreign keys were not even designated as such. — fadedbee, Feb 20 '21 at 08:18
An id is just a unique identifier for a single record in a single database. Other records in other tables might reference to this is id, but that's it. Very simple and straightforward, works flawlessly in any database for the last couple of decades. Why would you make things more complicated? It can only fail. And it will — Frank Heikens, Feb 20 '21 at 09:14

Erwin Brandstetter · Accepted Answer · 2021-02-20T10:22:22.877

As has been commented, there are good reasons not to go there. Canonically, a surrogate PK is a unique identifier for rows within its table without carrying additional information.

I have not had cases of "wrong-table errors" or "confusion with iteration counters", yet. Reducing the key space of an integer (int4) PK by 99% may come around to bite you later, as well as limiting yourself to a maximum of 100 tables. If you start relying on the meaning of the number (which I would advise against), you better make sure that new tables stick to the rules. And nobody inserts manual ID values or alters ID values ...

That said, if you go there, consider:

CREATE SEQUENCE users_id_seq MINVALUE 1 INCREMENT 100;  -- ①
CREATE TABLE users (
    users_id bigint PRIMARY KEY DEFAULT nextval('users_id_seq'), -- ②
    ...
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

CREATE SEQUENCE txns_id_seq MINVALUE 2 INCREMENT 100;
CREATE TABLE txns (
    txns_id bigint PRIMARY KEY DEFAULT nextval('txns_id_seq'),
    ...
);
ALTER SEQUENCE txns_id_seq OWNED BY txns.id;

① Use MINVALUE 1 INCREMENT 100, MINVALUE 2 INCREMENT 100 etc. instead of manual calculation.

② Use bigint to more than make up for the reduced key space.
Also, it's odd that you'd put so much effort into unique IDs across the DB, and then use non-descriptive, duplicative column names like id. txns_id is a better naming convention.

Downside: more susceptible to setval() or ALTER SEQUENCE breaking the regime. Revoke the UPDATE privilege on sequences from all plain users.

What are the disadvantages in using IDs suffixed by a table identifying number?

1 Answers1