4 ms·
well-defined natural key (users and their logins, What if you want to offer an option to change user login name later? This is a headache if the rest of your d
by wladimir 15y ago
well-defined natural key (users and their logins,
What if you want to offer an option to change user login name later? This is a headache if the rest of your database points to your UID by name.
Same thing if you are making a book database. Sure, in theory the ISBN is a unique book identifier, but what if this somehow doesn't hold (ie, same ISBN but different covers). When you use only the 'natural' identifier you're stuck. Or what if someone typed the ISBN wrong? In a distributed system it can be incredibly difficult to update all the references, for example in users's shopping carts.
For these reasons the canonical DB design advice seems to be to use "meaningless" unique identifiers for objects (doesn't matter if this is autoincrement or UUID etc).
- wulczer 15y agoThat's what ON UPDATE CASCADE is for. If you have a distributed system, generating unique identifiers and keeping referential consistency across the distributed parts is already a difficult problem. But that's a digression. If I ever get so many users I'll have to partition the user table across databases, I'll have much more trouble that just their IDs (and I'll be rich :) )
- wladimir 15y agoYes, but in practice it frequently happens that you refer to records in various different places. Either client-side in API-using software, or in a completely separate database, etc. In such cases, "Update all references in the world" will be entirely impractical and bug-prone.