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

51–60 of 182 posts

Re: Design better databases

#51

Post by mozumder »

Smudge wrote: post #47
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.

Because a restaurant menu needs horizontal scaling...

Re: Design better databases

#52

Post by gnaritas »

dhd415 wrote: post #36
gnaritas wrote: post #31

Earlier quoted context omitted.

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.

Every sequential GUID generation feature provides guarantees only per session. In other words, when you bring your database down to upgrade or patch it and then bring it back up, your GUID sequence starts over again at some arbitrary value which is likely to overlap with your existing GUIDs.

> your GUID sequence starts over again at some arbitrary value which is likely to overlap with your existing GUIDs.

I don't believe that's true. You're right that they start the session over, but it'll still be a fresh sequence that doesn't overlap anything generated in the past.

Re: Design better databases

#53

Post by Smudge »

mozumder wrote: post #51
Smudge wrote: post #47

Earlier quoted context omitted.

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.

Because a restaurant menu needs horizontal scaling...

Any data might need horizontal scaling regardless of the type of content. It all comes down to how much data and how often it is accessed. So, sure, if you're building the website for a single restaurant, don't use UUIDs, because no matter how popular, it will never get that much traffic.

Re: Design better databases

#54

Post by gnaritas »

jsmthrowaway wrote: post #50
gnaritas wrote: post #31

Earlier quoted context omitted.

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.

An int is either 4 or 8 bytes, it's still going to perform better no matter how you treat the GUID.

edit: bytes

Re: Design better databases

#55

Post by ww520 »

skrebbel wrote: post #17
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'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)

UUID as PK is perfectly fine; however, as with any design decision it really depends on your needs and weighing alternatives.

Some popular ways to do PK: natural key, sequentially generated key, and UUID. Personally I would prefer natural key if I can find an immutable natural key for the table. However, natural keys are hard to find. The food example in this case doesn't have natural key. Also if the natural key requires a compound key, it's just not worth the pain.

Sequential auto generated key is good when you need to hand it off to users, like order id or ticket number. It's short and simple and it's auto generated. The downsize is when you migrate databases, you need to seed the new database carefully or it would start from beginning again. Also in record creation, you need to do extra read to get back the newly generated key.

UUID is mostly worry free. It can be generated anywhere and doesn't need to be in the database. For a setup with distributed databases, I would use UUID just to have global uniqueness. For an offline app, I would use UUID to create the data records locally and later sync them with the main database. UUID is good when it's used internally and not exposed to the users.

Re: Design better databases

#58

Post by jon-wood »

ww520 wrote: post #55
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 :)

UUID as PK is perfectly fine; however, as with any design decision it really depends on your needs and weighing alternatives. Some popular ways to do PK: natural key, sequentially generated key, and UUID. Personally I would prefer natural key if I can find an immutable natural key for the table. However, natural keys are hard to find. The food example in this case doesn't have natural key. Also if the natural key req…

Sequential IDs can also leak business information when exposed to customers such as how many orders your taking - an interested party can place an order at 10am and another at 11am. Once you've done that you can compare the IDs and know how many orders were taken that hour.

Re: Design better databases

#59

Post by Tenhundfeld »

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…

I actually prefer just using id as the primary key, and I like the explicitness of seeing the table/alias before the column in complex queries. I don't care too much about typing it out; reservation.id isn't longer than reservation_id, and the savings of USING vs ON seem minimal. I also don't care about deduplicating that one field, as I'll likely need to consider other duplicate fields in the results, like created_at or description.

But I can see arguments in both directions. I think the big thing is consistency. If you consistently use _id as the PK and your tooling doesn't fight it, then cool.

I do feel like this benefit breaks down rather quickly though. For example, if you wanted to track who created a guest record, you probably wouldn't name the column user_id as it's ambiguous. So you might use creator_id to signify it points to the user who created the guest, not to the user invited to the reservation – thus negating your ability to join to a user table via USING.

That's not to say it's a bad tip, just because there are cases where it doesn't fit. Definitely worth knowing. Thanks for sharing.

Re: Design better databases

#60

Post by ciroduran »

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…

And if you're using a schema you did not create, don't forget to use shorthands! SELECT * FROM reservation r JOIN guest g ON (r.id = g.reservation_id), queries can get messy if you don't use them! :)
Post reply on HN