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

111–120 of 182 posts

Re: Design better databases

#111

Post by erpellan »

jessedhillon wrote: post #68
Tenhundfeld wrote: post #59

Earlier quoted context omitted.

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_a…

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

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

Re: Design better databases

#113

Post by irl_zebra »

ww520 wrote: post #65
irl_zebra wrote: post #64

Earlier quoted context omitted.

Why should UUIDs not be exposed to users? Because of the their unwieldy look, or because of security concerns? I ask because I have an app where previously I was accessing a particular data point via pk, and the user saw the pk in the url bar. But it could expose user/site data as any user could access that pk, or just guess at the next sequentially generated pk. I switched to uuid and now it's a little ugly in the u…

I meant the key handed off to the user for human interaction. As a user I would prefer order number 7356 than 7458e3a9-716b-4352-b2e4-b5b67d0c089b. If there's no human interaction with the key, UUID is perfectly fine.

Gotcha, just wanted to make sure I wasn't missing something. Makes total sense, thanks!

Re: Design better databases

#114

Post by kbsletten »

greggyb wrote: post #99
daxelrod wrote: post #88

Earlier quoted context omitted.

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?

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

Re: Design better databases

#116

Post by slagfart »

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?

Explicit foreign keys perform better in some joins, as the optimiser can assume that all possible values are in the source column of that foreign key.

Re: Design better databases

#117

Post by sokoloff »

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 the columns are; this is just for concreteness.)

select * from A JOIN B using (id) will give you a three column result set, id, name, value.

Now, suppose you want to do a schema modification to add "last _login" to A. Suddenly, your query is returning a 4 column result set, id, name, last_login, value and code using ordinal positions is now reading last_login into the "value" slot.

Maybe it's a corner-case for our use case and zero-downtime release processes, but it's something to be aware of anyway.

Re: Design better databases

#118

Post by slagfart »

Retra wrote: post #102
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…

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.

Having a sort order is immensely preferable when you're joining multiple sequential rows at once. The optimiser can create a pseudo-partition for your join, and constrain its reads to that. Especially on spinning rust (or I/O constrained systems), you're also reading multiple sequential pages in a stream off disk, which gives a big speed boost compared to random reads.

Without that ordering, the index is 'dumb', and has to find all your rows one by one in its structure. Admittedly it's not as much of an issue for SSDs, but it does still impact performance.

Re: Design better databases

#119

Post by Someone »

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?

I have longed for that, too, but I'm not sure I should want it.

Problem is that it becomes problematic as soon as you keep such queries around (in stored procedures, source code, or batch files), and then modify your schema by adding or removing a foreign key.

Re: Design better databases

#120

Post by bmdavi3 »

erpellan wrote: post #111
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.

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?
Post reply on HN