Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Design better databases

web.archive.org

Post reply on HN
Print view View original

41–50 of 182 posts

Re: Design better databases

#41

Post by tacone »

I like this site. As simple as it is, it has a lot of potential community-wise.

- it could allow sql exports for various platforms - it could feature an API so people can write their own framework drivers (for example creating migrations, importing existing schemas etc)

Lets hope that it gains users, so that the voting system (the star) can become more useful to filter out the garbage.

Edit: please remove the login wall to see the starred items, it raises the entry barrier quite a bit. Do you really want new visitors to see low rated schemas as the first thing?

Re: Design better databases

#42

Post by rlonstein »

mortehu wrote: post #35

Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like: SELECT * FROM reservation JOIN guest USING (reservation_id); By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in…

But, but, but ActiveRecord doesn't do that by default so it can't be a good idea [sarcasm]

Re: Design better databases

#43

Post by ww520 »

mvitorino wrote: post #25
skrebbel wrote: post #17

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 :)

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…

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 range query, you should pick something that can be sorted in the range anyway.

Re: Design better databases

#44

Post by rlonstein »

jordanlev wrote: post #16

If anyone is interested, there is a 2-volume set of books called "The Data Model Resource Book". They've been around for a while, so for more traditional businesses, but very thorough and broken out by industry: http://www.wiley.com/WileyCDA/WileyTitle/productCd-047138023... http://www.wiley.com/WileyCDA/WileyTitle/productCd-047135348...

+1. Definitely worth reading, I have Vols 1 & 2 (Vol 3 came out later). I found it useful because the author explained the decisions about the models and traps to avoid.

Re: Design better databases

#47

Post by Smudge »

klodolph wrote: post #12

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 don't disagree, but there is a case to be made for using UUID over sequential integer ids, and that is for horizontal scaling. When inserting records with UUID primary keys, you don't have to have your db cluster synchronized around which sequential ID should be next. There are other ways around this problem, of course, but using UUIDs sidesteps the issue entirely.

Re: Design better databases

#48

Post by enobrev »

mvitorino wrote: post #25
skrebbel wrote: post #17

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 :)

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…

So you're saying it's specifically less efficient for a write-heavy tables, correct?

I generally prefer auto-incremented integers, but UUIDs are very useful for client-generated records (like for an app/website that's built for offline usage).

Re: Design better databases

#49

Post by petepete »

mortehu wrote: post #35

Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like: SELECT * FROM reservation JOIN guest USING (reservation_id); By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in…

Or if you're feeling lucky:

SELECT * FROM reservation NATURAL INNER JOIN guest;

Re: Design better databases

#50

Post by jsmthrowaway »

gnaritas wrote: post #31
mvitorino wrote: post #25

Earlier quoted context omitted.

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…

That would be true except for that fact that database designers know this, and offer a way to generate sequential GUID's; so it's actually not a problem for that reason. The only real downside is performance, an int key performs better.

> an int key performs better.

A UUID is a 128-bit integer. That people do not store, generate, or interact with them that way is the bug.

I'd kill for a 128-bit architecture so a UUID compare would be a single instruction, and it's too bad consensus is that we don't really need it.

Post reply on HN