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

91–100 of 182 posts

Re: Design better databases

#91

Post by developer2 »

leepowers wrote: post #66
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 prefer longer, more descriptive table names coupled with shorter columns names. Then use aliases when writing queries. SELECT * FROM long_table_name l LEFT JOIN another_table_here a ON l.id = a.rel_id Also, most tables end up being used to populate objects. It's simpler to reference an object with `Reservation.id` than `Reservation.reservation_id` Regardless of the design choices made it's more important to be cons…

I tabbed away from my previous comment for a minute, which made me forget to mention the ORM scheme in applications. You're exactly right.

Nobody ever uses the class "Reservation" with a property named "reservation_id" in their code. They will name their column "reservation_id" in the database, but then work to undo that at the application level by telling the ORM to map "reservation_id" to an object property named "id". It creates the situation where developers fight against the database schema in their code, and adds an unnecessary level of complexity when comparing code to database. "Why is it 'id' in code but 'reservation_id' in the database?".

A common counterargument from pure DBAs, who are not themselves writing code that integrates with the schemas they create, is that databases should be approached as a completely standalone component that should not be designed with applications' structures in mind. That applications are free to bastardize the database schema in any way they please, so it really doesn't matter anyway. I personally prefer to see it as a reality that applications will be the primary consumer of the database resource, and deserve to have their intended integrations analyzed. Once the database is finalized, it is the application developers who will be studying the schema on a weekly basis. Anything to ease that constant inspection process, the better.

Of course, this is all personal preference. I have learned over the years to finally stop arguing so much over these little details. In real life with coworkers, anyway. It's still fun to battle for your point of view with forum strangers! :D

Re: Design better databases

#92

Post by mortehu »

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.

> I'd kill for a 128-bit architecture so a UUID compare would be a single instruction

Well, there's PCMPESTRI on Intel.

http://www.felixcloutier.com/x86/PCMPESTRI.html

Re: Design better databases

#94

Post by kazinator »

hghar wrote: post #89

I don't think this must be called "patterns" in computer science patterns are a model applied to give solutions to recurring problems. I was expecting something like Martin Fowler Patterns-Enterprise-Application-Architecture but for databases. This should be called something like database designs.

So what you're saying is that "design patterns" should only be for code, not for data?

If you apply some recipe to structure code, that's a "design pattern"; if we apply some recipe for structuring data, it's a "data(base) design"?

But code is data. But sometimes it's not clear whether something is code or data, or whether it's closer to being code than to data.

Design patterns incorporate data. The Observer pattern requires a list of observers that are notified; that is a data structure which we can have in a database: an observer table joined to an event source by event source ID.

If I make such a database (say for a large number of users to set up persistent notifications about some interesting interesting things), am I doing "database design" or implementing the Observer pattern? :)

Re: Design better databases

#95

Post by developer2 »

fleitz wrote: post #80
developer2 wrote: post #67

Earlier 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?

>> 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.

Re: Design better databases

#96

Post by Jweb_Guru »

ddorian43 wrote: post #75
ww520 wrote: post #43

Earlier 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.

It's pretty variable, but Postgres definitely is not the only one that does it that way (or even close).

Re: Design better databases

#97

Post by clavalle »

gcr wrote: post #22
clavalle wrote: post #18

Earlier quoted context omitted.

There is no way to enforce data integrity between related tables by virtue of the data model for one thing.

I don't get this argument. Foreign key constraints are part of the schema. What should the primary key be if not a UUID?

A natural key. It is not always possible, but when it is it should be used.

Re: Design better databases

#98

Post by jsmthrowaway »

gnaritas wrote: post #87
jsmthrowaway wrote: post #84

Earlier quoted context omitted.

Which would still make the low-order quadword not match.

It's a database though, you don't get to control how comparisons are done. Guid's are larger than ints, so using them as keys or joining on them is going to be slightly slower (not that it makes them bad, I'm not against GUID's). Just as using a bigint will be slower than an int.

If I were implementing the uuid type in Postgres and did anything other than `if(unlikely(LO(uuid1) == LO(uuid2)) && unlikely(HI(uuid1) == HI(uuid2)))` I'd consider myself doing the wrong thing, personally. Two 64-bit compares, two flag checks, and a branch, which is about as fast as it's going to get in the absence of a 128-bit architecture.

You could actually vectorize the compares in SSE, I believe (but I stopped writing assembly before SSE existed and don't know much about it at a low level). This is why it's so critical to treat a UUID as an integer instead of a string, and I see a lot of code that does the latter.

Re: Design better databases

#99

Post by greggyb »

daxelrod wrote: post #88
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…

It's long bothered me that SQL doesn't have a way of saying "join these two tables based on the key relationships I've already defined in my schema". (Or maybe some variants do?) The database already knows that guest.reservation_id is a foreign key referencing reservation.id. Why should you have to repeat yourself?

And when there are multiple foreign keys between two tables?

Re: Design better databases

#100

Post by jsmthrowaway »

mortehu wrote: post #92
jsmthrowaway wrote: post #50

Earlier quoted context omitted.

> 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.

> I'd kill for a 128-bit architecture so a UUID compare would be a single instruction Well, there's PCMPESTRI on Intel. http://www.felixcloutier.com/x86/PCMPESTRI.html

Yeah, I just said this in another comment, but SSE is my weak point; I stopped writing assembly and dealing with this stuff before SSE was even introduced.

That looks basically like what you need, though, yeah. Cool.

Post reply on HN