Had a quick look through, don't understand what audience this is targeted towards.
Design better databases
101–110 of 182 posts
Re: Design better databases
#102Earlier quoted context omitted.
I'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)
The problem is that most uuids are generated in a way that, when sorted alphabetically as strings, would have a random order. 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 da…
I don't understand this... How does the sort order in the index have anything to do with the arrangement of the table? If you build an index over the UUIDs, it's just going to refer to rows in the table by their internal offsets.
Re: Design better databases
#103It'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...
That healthcare industry data model is very simplistic and would not be adequate for the majority of real world use cases involving patient visits. If you have to model something in the healthcare domain then don't reinvent the wheel. Start with the HL7 RIM, and then constrain it down to what you really need.
https://www.hl7.org/implement/standards/product_brief.cfm?pr...
Re: Design better databases
#104Earlier quoted context omitted.
those models are actually pretty bad the ones in these books are better: https://dba.stackexchange.com/questions/12991/ready-to-use-d...
This strikes me as a very unhelpful comment. Why are they bad? Why are the ones in these books better?
The site (databaseanswers) is neat and I've gotten some good ideas from it in the past, but most of the models (that I've looked at anyway) are pretty simple and you almost really wouldn't need to look at a diagram drawn by somebody else to intuitively put something like that together. It seems almost more for someone creating MS Access level applications. Granted there may be some more complex schemas that I didn't see.
There might be an argument about excessive normalization in some cases also. Take some of those layouts too far and try to extend them and you might wind up with tons of little tables. Normalization was I think more important back in those days (not that it's not still important... but some of the downsides of going overboard on normalization have become apparent I think.... at least to me).
Re: Design better databases
#105I 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 :)
Came across an interesting case against using UUIDs the other day, in the UK it's a legal requirement that VAT invoices are sequential:
http://www.hmrc.gov.uk/manuals/vatrecmanual/vatrec5020.htm
Using random UUIDs as an invoice number wouldn't cut it, you'd have to have a sequential ID in some form. You could of course have both but that's adding unnecessary complexity.
Another example, if you are designing a website and lets say give each blog post a UUID, a URL of:
website.com/blog/my-post-34
Looks better than
website.com/blog/my-post-d173affb-6bb8-4435-bef1-4e29409dff4c
Unique sequential Id's also give some clues about the relative age of rows (eg ID 100 is older than ID 50). This doesn't come up often but can sometimes be useful.
Regardless, I always find using a unique int ID for pretty much every record makes your job 10x easier and I fail to see why you would ever want to do anything different. If you want to 'hide' the ID in special cases (eg order numbers) better to generate a unique random int ID.
Re: Design better databases
#106Earlier quoted context omitted.
This is a non-issue. Most db engines just append new data rows at the end of the data table and assign an internal row ID for it. The keys (UUID in this case) are stored in the separate index pages using B+ tree, which searches random key (UUID) or sequentially incremented key equally well. UUID key is a problem only if your main query is a range query on the PK of a clustered indexed table. If your main query is a r…
Actually only postgresql does it this way (by storing data in the heap and not in the primary index). Mysql(innodb),mssql,oracle uses the store the row in the primary-key. Edit: I'm ~wrong, see below.
[deleted]
Re: Design better databases
#107Earlier quoted context omitted.
I once made that argument, we ended up with reservation_name, I regret I ever said anything about it. Apparently, it made joins more clear, I seriously wonder how often people fucked that up for them to think it was a good idea to prefix every fucking column with its table name. It's like when I see unit tests for setters and think, gee, setters seem pretty straightforward to me, how often to people fuck them up?
>> we ended up with reservation_name That's absolutely terrifying. You've just provided my subconscious with new material with which to populate my nightmares. How did the code using that database operate? Were you using "reservation_name", or did the application revert the naming scheme by mapping the column to "name"? Either way, FML.
Could you elaborate on why this is so awful? (genuine question)
Re: Design better databases
#108Earlier quoted context omitted.
I once made that argument, we ended up with reservation_name, I regret I ever said anything about it. Apparently, it made joins more clear, I seriously wonder how often people fucked that up for them to think it was a good idea to prefix every fucking column with its table name. It's like when I see unit tests for setters and think, gee, setters seem pretty straightforward to me, how often to people fuck them up?
>> we ended up with reservation_name That's absolutely terrifying. You've just provided my subconscious with new material with which to populate my nightmares. How did the code using that database operate? Were you using "reservation_name", or did the application revert the naming scheme by mapping the column to "name"? Either way, FML.
FFS, it's just reservation.reservation_name, it's not like he's parsing HTML with regular expressions or something. Like... big deal.
Re: Design better databases
#109Earlier quoted context omitted.
Not really a fan. When your table name is "reservation", you're prefixing "id" with the table name ("reservation_id"), but you don't do the same for the other columns. I've never liked the inconsistency of having "reservation_id", but other columns like "name" instead of "reservation_name". Especially on longer table names where you wind up with columns like "this_really_long_table_id". All just to shorten join claus…
I once made that argument, we ended up with reservation_name, I regret I ever said anything about it. Apparently, it made joins more clear, I seriously wonder how often people fucked that up for them to think it was a good idea to prefix every fucking column with its table name. It's like when I see unit tests for setters and think, gee, setters seem pretty straightforward to me, how often to people fuck them up?
Yes, you always have to be really careful when positing an absurd solution to make a point; there's always the risk you'll be taken seriously. :)
Re: Design better databases
#110Earlier quoted context omitted.
>> we ended up with reservation_name That's absolutely terrifying. You've just provided my subconscious with new material with which to populate my nightmares. How did the code using that database operate? Were you using "reservation_name", or did the application revert the naming scheme by mapping the column to "name"? Either way, FML.
FFS, it's just reservation.reservation_name, it's not like he's parsing HTML with regular expressions or something. Like... big deal.
I'm not sure if this is humor going over my head, or seriousness smacking me in the face.