21–30 of 182 posts
Earlier quoted context omitted.
I'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)
There is no way to enforce data integrity between related tables by virtue of the data model for one thing.
Earlier quoted context omitted.
I'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)
There is no way to enforce data integrity between related tables by virtue of the data model for one thing.
What should the primary key be if not a UUID?
I just clicked on the second "featured" pattern and found this hot mess: http://dbpatterns.com/documents/5091f74289cbad03bc958bc0/ It has the "let's put a UUID on every row" disease common to designers who have never really learned anything other than object oriented design. Price is a string (I guess so you can put "market price" on the fish?), and there's a currency symbol on every "delivery". The whole thing just…
I'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)
Others embrace surrogate keys because: They make joins between tables easier (usually one field rather than a compound key where one might accidentally not include all the join predicates). They save space since an integer is almost always smaller than a compound key.
So if you are in the camp that favors surrogate keys, then consider these points:
Some quick downsides for UUIDs: If your table has a clustered index, UUIDs are generally not created in a regular order, and so you'll be constantly inserting into a part of your index rather than the end. Some databases allow sequential UUID generation to help mitigate this. UUIDs are usually 16 bytes, but an int is usually 1-8 bytes. Also keep in mind you pay this price multiple times since the primary key (generally) becomes the foreign key in other tables.
Some upsides for UUIDs: If you have a process that has no master, being able to tie disparate things together with a shared UUID is very useful sometimes.
Often developers use UUIDs/GUIDs because: Their app wants to perform a 'Create' operation and they want to generate the key in advance.
This can usually be mitigated by creating a routine that generates a range of reserved surrogate integer keys, and gives a starting value to the application layer. Then the application layer can use those values with knowledge they won't already exist. For instance, the app layer say, "I'm going to make 100 customers", and the response comes back "10232". Meaning that 10132-10232 is reserved for that process to use for customer primary keys. This allows larger (chunkier) requests which can be interrelated without constantly making requests to the Database layer. One could extent this to be a true cache for entity reserved numbers that gets dolled out as needed by the app layer.
It's an old-looking website, but I've found this site has some really cool data models: http://www.databaseanswers.org/data_models/index.htm As someone in the healthcare space, looking at some of these models gives me a better idea of how various aspects of the healthcare industry work, and the things they interact with. Ex) http://www.databaseanswers.org/data_models/patient_data_ware...
those models are actually pretty bad the ones in these books are better: https://dba.stackexchange.com/questions/12991/ready-to-use-d...
I just clicked on the second "featured" pattern and found this hot mess: http://dbpatterns.com/documents/5091f74289cbad03bc958bc0/ It has the "let's put a UUID on every row" disease common to designers who have never really learned anything other than object oriented design. Price is a string (I guess so you can put "market price" on the fish?), and there's a currency symbol on every "delivery". The whole thing just…
I'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)
Example: t0: 7458e3a9-716b-4352-b2e4-b5b67d0c089b t1: 4d8d753c-1777-439d-8725-b093b1bd8430
Using this as a PK in any relational database will mean the rows are stored in the clustered index order, which causes extreme fragmentation because the db engine constantly has to find "holes" in the data pages to insert more recent records instead of adding those at the bottom of the table as would happen with any continuously increasing key.
It's an old-looking website, but I've found this site has some really cool data models: http://www.databaseanswers.org/data_models/index.htm As someone in the healthcare space, looking at some of these models gives me a better idea of how various aspects of the healthcare industry work, and the things they interact with. Ex) http://www.databaseanswers.org/data_models/patient_data_ware...
those models are actually pretty bad the ones in these books are better: https://dba.stackexchange.com/questions/12991/ready-to-use-d...
Any company will get to a point where they're app is just slow and they need to use something better like MongoDB or Elasticsearch.
I just clicked on the second "featured" pattern and found this hot mess: http://dbpatterns.com/documents/5091f74289cbad03bc958bc0/ It has the "let's put a UUID on every row" disease common to designers who have never really learned anything other than object oriented design. Price is a string (I guess so you can put "market price" on the fish?), and there's a currency symbol on every "delivery". The whole thing just…
I'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)