4 ms·

Hard disagree. You create tables for them to be queried by applications. For the Restaurants example the application clearly wasn't designed to handle Restauran

Hard disagree. You create tables for them to be queried by applications. For the Restaurants example the application clearly wasn't designed to handle Restaurant entries whose only field differs is rank. Consequently, you shouldn't allow data the application can't handle. Whenever the time comes and you think "ah, wouldn't it be great if multiple people could review the same restaurant in the same year?" you change the natural keys. You can always relax the rules. Tightening the rules is much harder because garbage data may already be present. Keys are about the integrity of your application(s) and preventing corner cases by making them impossible.

konschubert 2y ago
You can use a UUID as a primary key and still enforce uniqueness on a combined index of city and name.
- nkozyra 2y ago
  If they conflict and are user facing identifiers aren't you then forced to add uniqueness to the city and name, ala /chicago/chipotle_s4, potentially bleeding some business details? I don't love uuids as public identifiers for a number of reasons but not hinting details about your data is one nice thing about them.
  - konschubert 2y ago
    Hmm, I am not sure if I get you. The cool thing about enforcing uniqueness on a secondary index is that you can just change or remove the uniqueness constraint anytime without breaking foreign key relations.
    - nkozyra 2y ago
      I'm not sure why you want a uniqueness index then in this case. If it's not publicly visible (effectively a secondary index / reference), then you can't use it as an identifier. This means you need something else (a slug, as discussed herein) or just use the ID (bad practice in general) or UUID (bad for humans). If you use - in this example - city and company name - you still have to enforce uniqueness, so you have a pseudo-slug anyway.
- bjourne 2y ago
  But what's the point? You're complicating the data for no apparent gain. Only terrible RDBMSes which don't support multi-column keys require surrogate keys.
  - konschubert 2y ago
    Because you can remove or change the uniqueness constraint without having to worry about foreign key relationships.
    - bjourne 2y ago
      Eh, that's a feature of natural keys. If you try to fuck up your model your RDBMS will complain.
zarzavat 2y ago
You can’t change primary keys, that’s the point, because you don’t know where they are. For example if an old key is in a URL, and that URL is in a browser bookmark, now you need redirects, so you need to keep all the old keys around forever. Keys should be random or sequential, never contain information. If you want to enforce uniqueness then use a unique index/constraint.
- bjourne 2y ago
  I think you are confused about the terminology because a primary key is a uniqueness constraint. They can be changed in any RDBMS worth its salt. Keys should not be random and your URL example is a case in point. The url /germany/berlin/2023/mcdonalds contains no surrogate key and is immensely more useful than the url /review?uuid=1kjksdhh3244ygdvvgdd2345.
  - zarzavat 2y ago
    A primary key implies a uniqueness constraint. But you can have a uniqueness constraint without a primary key. You can change primary keys within a database but you can’t change them outside the database. A database is a map but keys are part of the territory. The second URL with the UUID is more secure and is preferred in many situations where information leakage is a concern. The first URL is more descriptive but more prone to breakage. It depends on the situation.
    - bjourne 2y ago
      A key is any set of unique columns. The primary in primary key just hints to the RDBMS how to store the data. Both URLs encode queries on the data. The first says "give me the 2023 review(s) of McDonald's in Berlin" (i.e., it has semantics) and the second "give me the row with uuid 1kjksdhh3244ygdvvgdd2345". I don't think the second URL is more robust because, for example, if the row has to be recreated it will fail to work.

konschubert 2y ago

You can use a UUID as a primary key and still enforce uniqueness on a combined index of city and name.

nkozyra 2y ago
If they conflict and are user facing identifiers aren't you then forced to add uniqueness to the city and name, ala /chicago/chipotle_s4, potentially bleeding some business details? I don't love uuids as public identifiers for a number of reasons but not hinting details about your data is one nice thing about them.
- konschubert 2y ago
  Hmm, I am not sure if I get you. The cool thing about enforcing uniqueness on a secondary index is that you can just change or remove the uniqueness constraint anytime without breaking foreign key relations.
  - nkozyra 2y ago
    I'm not sure why you want a uniqueness index then in this case. If it's not publicly visible (effectively a secondary index / reference), then you can't use it as an identifier. This means you need something else (a slug, as discussed herein) or just use the ID (bad practice in general) or UUID (bad for humans). If you use - in this example - city and company name - you still have to enforce uniqueness, so you have a pseudo-slug anyway.
bjourne 2y ago
But what's the point? You're complicating the data for no apparent gain. Only terrible RDBMSes which don't support multi-column keys require surrogate keys.
- konschubert 2y ago
  Because you can remove or change the uniqueness constraint without having to worry about foreign key relationships.
  - bjourne 2y ago
    Eh, that's a feature of natural keys. If you try to fuck up your model your RDBMS will complain.

zarzavat 2y ago

You can’t change primary keys, that’s the point, because you don’t know where they are. For example if an old key is in a URL, and that URL is in a browser bookmark, now you need redirects, so you need to keep all the old keys around forever. Keys should be random or sequential, never contain information. If you want to enforce uniqueness then use a unique index/constraint.

bjourne 2y ago
I think you are confused about the terminology because a primary key is a uniqueness constraint. They can be changed in any RDBMS worth its salt. Keys should not be random and your URL example is a case in point. The url /germany/berlin/2023/mcdonalds contains no surrogate key and is immensely more useful than the url /review?uuid=1kjksdhh3244ygdvvgdd2345.
- zarzavat 2y ago
  A primary key implies a uniqueness constraint. But you can have a uniqueness constraint without a primary key. You can change primary keys within a database but you can’t change them outside the database. A database is a map but keys are part of the territory. The second URL with the UUID is more secure and is preferred in many situations where information leakage is a concern. The first URL is more descriptive but more prone to breakage. It depends on the situation.
  - bjourne 2y ago
    A key is any set of unique columns. The primary in primary key just hints to the RDBMS how to store the data. Both URLs encode queries on the data. The first says "give me the 2023 review(s) of McDonald's in Berlin" (i.e., it has semantics) and the second "give me the row with uuid 1kjksdhh3244ygdvvgdd2345". I don't think the second URL is more robust because, for example, if the row has to be recreated it will fail to work.