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

31–40 of 182 posts

Re: Design better databases

#31

Post by gnaritas »

mvitorino wrote: post #25
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 :)

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…

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.

Re: Design better databases

#32

Post by haddr »

Cool website, for off the shelf models. For anything more advanced you should be careful, as modeling real world scenarios is rarely so abstract and mamy times you need to make some scenario-specific tradeoffs during the modeling phase to fulfill your requirements.

Re: Design better databases

#33

Post by wiredfool »

mvitorino wrote: post #25
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 :)

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 believe that the Primary Key affects the physical ordering of records in PostgreSQL.

Primary Key is implemented as a unique index coupled with a not null constraint. It's essentially syntactic sugar.

Re: Design better databases

#34

Post by lai »

clay_to_n wrote: post #6

It's an old-looking website, but I've found this site has some really cool data models: http://www.databaseanswers.org/data_models/index.htm As someone in the healthcare space, looking at some of these models gives me a better idea of how various aspects of the healthcare industry work, and the things they interact with. Ex) http://www.databaseanswers.org/data_models/patient_data_ware...

I think that after you posted this, it caused a surge of traffic to that website, which caused the server to crash lol.

Re: Design better databases

#35

Post by mortehu »

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 query, and "SELECT *" will return only one copy.

Re: Design better databases

#36

Post by dhd415 »

gnaritas wrote: post #31
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…

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.

Re: Design better databases

#37

Post by Mister_Snuggles »

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…

But where do you even put the price on a food item to start with?

The only price is on the 'delivery' table, which seems to imply that the price is only set when someone orders something. I guess this is a delivery-only place? But because the 'delivery' table can only have one food_uuid, you'd have to put in for a bunch of deliveries to order more than one thing.

Maybe the 'price' on the menu is in that 'Additional Info' table?

    SELECT A.NAME, A.DESCRIPTION, B.VALUE FROM FOOD A LEFT OUTER JOIN "Additional Info" B ON (A.UUID = B.UUID AND B.TYPE = 'Price');
There's so much wrong here...

Re: Design better databases

#38

Post by wahsd »

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…

Did it cross your mind that there is a whole host of reasons why it may have 18 stars that have nothing to do with the quality of the pattern?

Re: Design better databases

#40

Post by mortehu »

wiredfool wrote: post #33
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 believe that the Primary Key affects the physical ordering of records in PostgreSQL. Primary Key is implemented as a unique index coupled with a not null constraint. It's essentially syntactic sugar.

To elaborate, even if you do call CLUSTER[1] to enforce a physical ordering, the primary key is not the default (there is no default).

1. http://www.postgresql.org/docs/9.5/static/sql-cluster.html

Post reply on HN