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

121–130 of 182 posts

Re: Design better databases

#121

Post by gnaritas »

jsmthrowaway wrote: post #98
gnaritas wrote: post #87

Earlier quoted context omitted.

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…

While all of that is true, it's really not relevant to a programmer deciding on which to use as a key; he has no control over any of those things.

Re: Design better databases

#122

Post by bottled_poe »

vmasto wrote: post #107
developer2 wrote: post #95

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

Could you elaborate on why this is so awful? (genuine question)

I can think of a few reasons:

- The field name implies that the primary key field is a string. This entails numerous issues (eg. How do you generate a new unique ID?) - It is not obvious that the field is the primary key. - A 'name' property on a reservation doesn't make sense. Is it the name of the person making the reservation? How can this be unique? etc.

Re: Design better databases

#123

Post by jpitz »

bmdavi3 wrote: post #120
erpellan wrote: post #111

Earlier quoted context omitted.

Some things that I'd love to see as options in rdbms: Enforced globally unique column names - natural joins on everything. Throw an exception if a query requires a table scan (bonus points if it printed the statement that would create the appropriate index). Translate all update and delete statements into inserts (enforced immutable data + versioning).

For queries returning results from a majority of rows in the table, won't a sequential table scan be faster than an index scan?

For non-clustered indices, yes.

Re: Design better databases

#124

Post by level3 »

kbsletten wrote: post #114
greggyb wrote: post #99

Earlier quoted context omitted.

And when there are multiple foreign keys between two tables?

Genuinely curious, what's the use-case for this?

Some examples:

In project management, tasks might have an assigner_id and assignee_id

In transaction management, transactions might have a sender_id and receiver_id

In sports, matches might have a team1_id and team2_id

Re: Design better databases

#125

Post by jessaustin »

fleitz wrote: post #82
Mahn wrote: post #76

Earlier quoted context omitted.

Personally, for me it makes sense that the id field has a different distintive name for each table because it's a specific kind of id, i.e., customerID is not interchangeable with reservationID. For me, this is not so much about prepending the field with the table name, but about calling things by their name. "id" is just too vague, even if you use it in the context of a specific table.

I believe this is what foreign keys are for, and also, they are usually both ints/uuids/etc so generally they are interchangeable. I fail to see how customer.id = product.id is any less obvious of a bug than customer.customerID = product.productID

With aliases that first expression might be:

  c.id = p.id
That seems like an easy error to make...

Re: Design better databases

#126

Post by level3 »

sokoloff wrote: post #117
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 don't like using select * as it will cause the shape of the result set to change upon schema modifications. We use a DB access layer that "knows" the column indices of result sets at compile time and our zero-downtime process relies on that not changing (for the time period where old code is running against new schema). Imagine two two-column tables, A with id and name, and B with id and value. (Doesn't matter what…

I'm curious: what are the advantages of having your access layer rely on column indices rather than column names?

Re: Design better databases

#127

Post by lccarrasco »

sokoloff wrote: post #117
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 don't like using select * as it will cause the shape of the result set to change upon schema modifications. We use a DB access layer that "knows" the column indices of result sets at compile time and our zero-downtime process relies on that not changing (for the time period where old code is running against new schema). Imagine two two-column tables, A with id and name, and B with id and value. (Doesn't matter what…

Most drivers or interfaces to the db let you use the column's name instead of the ordinal positions, precisely because that decouples the query's result from the things you care about, might be worth checking ^^

Re: Design better databases

#128

Post by ahachete »

Tenhundfeld wrote: post #85
jessedhillon wrote: post #68

Earlier quoted context omitted.

When you have to join on composite PKs, it's easier to appreciate the parent's advice.

Yeah, I hear that. I also tend to always use surrogate keys. It can have performance implications, but that's typically not my biggest concern. EDIT: I guess that doesn't really address your point of needing to join on a composite natural key though.

Do you know SSSKA? Society to Stop Surrogate Key Abuse: http://www.slideshare.net/PGExperts/keyvil-lightning-talk?qi... ;)

Re: Design better databases

#130

Post by sokoloff »

level3 wrote: post #126
sokoloff wrote: post #117

Earlier quoted context omitted.

I don't like using select * as it will cause the shape of the result set to change upon schema modifications. We use a DB access layer that "knows" the column indices of result sets at compile time and our zero-downtime process relies on that not changing (for the time period where old code is running against new schema). Imagine two two-column tables, A with id and name, and B with id and value. (Doesn't matter what…

I'm curious: what are the advantages of having your access layer rely on column indices rather than column names?

At coding-time (pre-compilation), we create classes representing each stored procedure that we've marked for code generation. That gives parameter list (inc auto-completion in the IDE), column names, types/type safety on inputs and outputs, etc, from the parameter list and result set shape and it was [slightly] easier to write the generic code that binds columns in the result set by column index, since we "knew" the column indices wouldn't change in the result set.

(We had a prior system that relied on column order and so had already banned select * and only allowed additions to the result set on the right side before we even got to the point of doing codegen or zero-downtime releases. This was 2003 that we created the codegen wrapper and 2005 or so when we switched to zero downtime releases.)

I guess "because we're lazy" is the somewhat overly glib answer. ;-)

Post reply on HN