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

151–160 of 182 posts

Re: Design better databases

#151

Post by huuu »

ComodoHacker wrote: post #144
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…

> the field will be automatically deduplicated, so you don't have to qualify it elsewhere in the query, and "SELECT " will return only one copy. You still have in outer joins. Also, SELECT * is a bad practice (with some exceptions) when used in code, suitable only for ad-hoc queries.

Agreed. Or at least put a table name in front of the asterisk:

  SELECT
      table1.*,
      table2.field
  FROM table1
  JOIN table2
      ON table2.rel_id = table1.id

Re: Design better databases

#152

Post by dspillett »

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.

>How does the sort order in the index have anything to do with the arrangement of the table?

If you have a clustered index based upon that column (and in systems that support this it is common for the primary key to be the clustered index for the table) then the physical layout of the data will follow this column.

See http://use-the-index-luke.com/blog/2014-01/unreasonable-defa... as a good run down of the whys and wherefores of clustered indexes.

Re: Design better databases

#153

Post by nodefortytwo »

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…

Yeah, i found that that too, spent 10 mins poking at it:

http://dbpatterns.com/documents/5707794a1514b4252236dcbe/

better, but not finished...

Re: Design better databases

#154

Post by ComodoHacker »

huuu wrote: post #151
ComodoHacker wrote: post #144

Earlier quoted context omitted.

> the field will be automatically deduplicated, so you don't have to qualify it elsewhere in the query, and "SELECT " will return only one copy. You still have in outer joins. Also, SELECT * is a bad practice (with some exceptions) when used in code, suitable only for ad-hoc queries.

Agreed. Or at least put a table name in front of the asterisk: SELECT table1.*, table2.field FROM table1 JOIN table2 ON table2.rel_id = table1.id

table.* doesn't really do any better. Database evolves, table's structure changes and you get various bugs and unpredictable behavior. From elusive exceptions to memory hogs to network bottlenecks, depending on DBMS, data itself and data access framework you use.

Re: Design better databases

#155

Post by xrstf »

Scirra_Tom wrote: post #105
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 :)

Came across an interesting case against using UUIDs the other day, in the UK it's a legal requirement that VAT invoices are sequential: http://www.hmrc.gov.uk/manuals/vatrecmanual/vatrec5020.htm Using random UUIDs as an invoice number wouldn't cut it, you'd have to have a sequential ID in some form. You could of course have both but that's adding unnecessary complexity. Another example, if you are designing a website…

Sequential order IDs are also mandatory in Germany. At least I remember that we had to make sure in one project that the order IDs had no gaps and were sequential.

Re: Design better databases

#156

Post by moron4hire »

These schemas seem like pretty simple, straightforward adaptations of specific problems. This doesn't actually look like any sort of actionable advice on how to design better schemas. Are we supposed to just osmose the knowledge somehow? I was expecting something more along the lines of recommendations like "foreign key relationships should always be indexed, nobody ever came up with a realistic example where they shouldn't! Why the hell isn't this the default behavior?"

Re: Design better databases

#157

Post by NicoJuicy »

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 like using id as key, if you want to "decouple" info, i just use reservation.id as reservation_id

This way, my db fetch is always on id and not a longer name ( reservation_id), which on the end of an webapp, makes me save some reading / writing :)

Re: Design better databases

#158

Post by huuu »

ComodoHacker wrote: post #154
huuu wrote: post #151

Earlier quoted context omitted.

Agreed. Or at least put a table name in front of the asterisk: SELECT table1.*, table2.field FROM table1 JOIN table2 ON table2.rel_id = table1.id

table.* doesn't really do any better. Database evolves, table's structure changes and you get various bugs and unpredictable behavior. From elusive exceptions to memory hogs to network bottlenecks, depending on DBMS, data itself and data access framework you use.

It does better when, for example, you have a field called 'id' in both tables.

  SELECT
      *
  FROM table1
  JOIN table2
      ON table2.rel_id = table1.id
will use one of the 'id' field from table1 or table2 (depending on the join type).

  SELECT
      table1.*
  FROM table1
  JOIN table2
      ON table2.rel_id = table1.id
will use the 'id' field of table1 and does not include data from table2.

But I agree with your other arguments.

Re: Design better databases

#159

Post by piers »

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 really hope you're using SELECT * as an example and that you don't really do that.

Re: Design better databases

#160

Post by igrekel »

Interesting idea but it the way it is currently done isn't that great.

Most of the patterns I've seen are obvious simple things but they are missing a lot of content like : - What was the design's intended properties? - Expected volumes, access patterns? - Good places for indices? Maybe the comments could be of some help but really haven't seen much. I've actually found more interesting content in the HN comments than on the site itself.

Post reply on HN