3 ms·
Always use surrogate primary keys. Use alternate keys (AK) for your "natural keys". Modify the AK business rules as and when needed. There are several advantage
by ak39 3y ago
Always use surrogate primary keys. Use alternate keys (AK) for your "natural keys". Modify the AK business rules as and when needed. There are several advantages to using meaningless random integers, sequential integers or UUIDs as your table's PK. The most important one is that if the PK participates (or is expected to participate) as a foreign key in other tables, it's crucial to us a design where you never ever have to change the PK!
- bruce511 3y agoAll this, but I'd steer away from sequential numbers. They suffer from security problems, data-merging problems, data-distribution and replication problems, and more. Over a long career I've come to favor UUID, the cost (space and performance) are well worth the advantages.
- scrollaway 3y agoUUIDv7 has all the advantages of UUID, plus sortability, plus an embedded timestamp which can replace your "created_at" thus reducing the cost aspect. It's also soon becoming an official part of the UUID spec. In fact, if your created_at is indexed and you can get rid of it via uuid7, there is practically no difference in terms of table size. The size cost is solely on foreign keys.
- evntdrvn 3y agoI really hope that I can find someone on the MS SQL Server team to champion implementing native UUIDV7 support, from what I've heard they are really hesitant to add more data types but a person's gotta try :)