5 ms·

"Where possible avoid simply using id as the primary identifier for the table." Has anyone had trouble by using surrogate primary keys? I've found the opposit

by Dowwie 10y ago

"Where possible avoid simply using id as the primary identifier for the table." Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.

jawns 10y ago
I disagree with this style point, and I think the author implicitly acknowledges that `id` should be the standard identifier, because further down he points out that the `_id` suffix should be used for columns that make reference to that identifier.
- jbverschoor 10y ago
  No he doesn't. In an example he actually uses the following: ON s2.mentor_id = s1.staff_num staff_num is the primary key To me this isan anti pattern because: - a primary should (can) never change; - composite primary keys are only useful (save space) in very large datasets It also conflicts with his pattern: uniform suffix _id
andreareina 10y ago
I think the author was talking about (not) using `id` as the column name, e.g. `user.user_id` instead of `user.id`
- webbore 10y ago
  ON second reading I think you are correct. I agree on this point then, as it helps when joining through a denormalized table that references multiple PK ids, ex: I think: user_id = role_id is more readable than the likely alternative: u.id = r.id
  - NickNameNick 10y ago
    it also lets you use the join foo using(foo_id) syntax, which I quite like
dahdum 10y ago
I disagree with this too, using table.id is easier to type and consistent when creating joins. It also encourages (forces) to reference the fields with tables prefix.
- ryeguy 10y ago
  One advantage of using user_id on both the pk of users as well as the fk side is you can do: INNER JOIN comments USING (user_id) instead of: INNER JOIN comments ON comments.user_id = user.user_id
  - philliphaydon 10y ago
    Which is fine if you are aware of the differences between the two. Since they act differently.
    - ryeguy 10y ago
      I don't think so, what differences are there? Could this be a database specific thing? In PG it seems equivalent.
      - philliphaydon 10y ago
        In MySQL you can end up with different results.
comboy 10y ago
Maybe what author means is that from the db design standpoint, composite keys are used much less often than they could be. Very often unique table row can be defined by some other column or a pair, on which you will probably put an index anyway. But that's just playing devil's advocate. In general I think ids are fine because ORMs play nice with them and FKs are simple.
- Treffynnon 10y ago
  Basically this is the answer - besides that bit about acquiescing to the demands of an ORM.
echelon 10y ago
Generated unique tokens are better than auto increment IDs. They can be created in a distributed multi-master setup, and they don't expose how many of a given record type you have to 3rd parties when exposing primary keys or feeds via APIs.
- dpcx 10y ago
  But also because they won't join incorrectly. That was the biggest win in explaining it to other devs.
forkandwait 10y ago
I am working with a 20 table db (not including lookups and session management tables) based on surrogate keys and it sucks ass for discoverability. It also subverts data checking based on SQL REFERENCES. I think natural / composite keys should used to design the db and establish relationships, surrogate keys added only if necessary, and then auto-generated, just like one waits to denormalize until there is a real need. I suspect the love of surrogate keys is just more MySQL bullshit pretending that classic SQL design is wrong merely because it is not implemented in that particular broken database.
- Dowwie 10y ago
  I'm not using mysql. Would you mind expanding what you mean by the struggles you've experienced during discoverability?
calpaterson 10y ago
I've had lots of problems with surrogate primary keys where people don't put enough constraints on the natural key and then duplicates (sometimes slightly different) get in. Unless you're using innodb, primary key is not special, it's just another index. If you decide to use surrogate keys you also need to enforce the natural key too (if possible). There are also some nice performance advantages you can get from natural keys with covering composite indices in the case where you only want the key. But in general a table can have multiple keys in all but the highest levels of normalisation.
- Dowwie 10y ago
  That's a good point about manually defining constraints on a table. A composite key forces a developer to think about uniqueness whereas the surrogate key makes that thought process optional.

by Dowwie 10y ago

jawns 10y ago

I disagree with this style point, and I think the author implicitly acknowledges that `id` should be the standard identifier, because further down he points out that the `_id` suffix should be used for columns that make reference to that identifier.

jbverschoor 10y ago
No he doesn't. In an example he actually uses the following: ON s2.mentor_id = s1.staff_num staff_num is the primary key To me this isan anti pattern because: - a primary should (can) never change; - composite primary keys are only useful (save space) in very large datasets It also conflicts with his pattern: uniform suffix _id

andreareina 10y ago

I think the author was talking about (not) using `id` as the column name, e.g. `user.user_id` instead of `user.id`

webbore 10y ago
ON second reading I think you are correct. I agree on this point then, as it helps when joining through a denormalized table that references multiple PK ids, ex: I think: user_id = role_id is more readable than the likely alternative: u.id = r.id
- NickNameNick 10y ago
  it also lets you use the join foo using(foo_id) syntax, which I quite like

dahdum 10y ago

I disagree with this too, using table.id is easier to type and consistent when creating joins. It also encourages (forces) to reference the fields with tables prefix.

ryeguy 10y ago
One advantage of using user_id on both the pk of users as well as the fk side is you can do: INNER JOIN comments USING (user_id) instead of: INNER JOIN comments ON comments.user_id = user.user_id
- philliphaydon 10y ago
  Which is fine if you are aware of the differences between the two. Since they act differently.
  - ryeguy 10y ago
    I don't think so, what differences are there? Could this be a database specific thing? In PG it seems equivalent.
    - philliphaydon 10y ago
      In MySQL you can end up with different results.

comboy 10y ago

Maybe what author means is that from the db design standpoint, composite keys are used much less often than they could be. Very often unique table row can be defined by some other column or a pair, on which you will probably put an index anyway. But that's just playing devil's advocate. In general I think ids are fine because ORMs play nice with them and FKs are simple.

Treffynnon 10y ago
Basically this is the answer - besides that bit about acquiescing to the demands of an ORM.

echelon 10y ago

Generated unique tokens are better than auto increment IDs. They can be created in a distributed multi-master setup, and they don't expose how many of a given record type you have to 3rd parties when exposing primary keys or feeds via APIs.

dpcx 10y ago
But also because they won't join incorrectly. That was the biggest win in explaining it to other devs.

forkandwait 10y ago

I am working with a 20 table db (not including lookups and session management tables) based on surrogate keys and it sucks ass for discoverability. It also subverts data checking based on SQL REFERENCES. I think natural / composite keys should used to design the db and establish relationships, surrogate keys added only if necessary, and then auto-generated, just like one waits to denormalize until there is a real need. I suspect the love of surrogate keys is just more MySQL bullshit pretending that classic SQL design is wrong merely because it is not implemented in that particular broken database.

Dowwie 10y ago
I'm not using mysql. Would you mind expanding what you mean by the struggles you've experienced during discoverability?

calpaterson 10y ago

I've had lots of problems with surrogate primary keys where people don't put enough constraints on the natural key and then duplicates (sometimes slightly different) get in. Unless you're using innodb, primary key is not special, it's just another index. If you decide to use surrogate keys you also need to enforce the natural key too (if possible). There are also some nice performance advantages you can get from natural keys with covering composite indices in the case where you only want the key. But in general a table can have multiple keys in all but the highest levels of normalisation.

Dowwie 10y ago
That's a good point about manually defining constraints on a table. A composite key forces a developer to think about uniqueness whereas the surrogate key makes that thought process optional.