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

131–140 of 182 posts

Re: Design better databases

#131

Post by clay_to_n »

nradov wrote: post #103
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...

That healthcare industry data model is very simplistic and would not be adequate for the majority of real world use cases involving patient visits. If you have to model something in the healthcare domain then don't reinvent the wheel. Start with the HL7 RIM, and then constrain it down to what you really need. https://www.hl7.org/implement/standards/product_brief.cfm?pr...

Good point! I've yet to dig into HL7 myself, so thanks for the link.

Re: Design better databases

#132

Post by developer2 »

bottled_poe wrote: post #122
vmasto wrote: post #107

Earlier quoted context omitted.

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.

My use of "name" was a poorly chosen example to tie to the concept of a reservation. I just chose it because "name" is an extremely common and unambiguous column in many tables.

Re: Design better databases

#133

Post by rukuu001 »

rlonstein wrote: post #44
jordanlev wrote: post #16

If anyone is interested, there is a 2-volume set of books called "The Data Model Resource Book". They've been around for a while, so for more traditional businesses, but very thorough and broken out by industry: http://www.wiley.com/WileyCDA/WileyTitle/productCd-047138023... http://www.wiley.com/WileyCDA/WileyTitle/productCd-047135348...

+1. Definitely worth reading, I have Vols 1 & 2 (Vol 3 came out later). I found it useful because the author explained the decisions about the models and traps to avoid.

> because the author explained the decisions

By far one of the most useful things a resource can do.

It doesn't just broaden your understanding of a domain, but it can teach you how to think about design problems.

Re: Design better databases

#134

Post by [deleted] »

developer2 wrote: post #95
fleitz wrote: post #80

Earlier quoted context omitted.

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.

[deleted]

Re: Design better databases

#135

Post by fleitz »

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)

Too much typing / line noise.

Re: Design better databases

#136

Post by voltagex_ »

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…

In which dialect?

Re: Design better databases

#138

Post by MichaelBurge »

jessedhillon wrote: post #69
petepete wrote: post #49

Earlier quoted context omitted.

Or if you're feeling lucky: SELECT * FROM reservation NATURAL INNER JOIN guest;

Err, be careful with natural joins: suppose both tables have a column called "name" or "created_at" -- natural join will create join conditions from those.

That's why he said, "If you're feeling lucky".

Re: Design better databases

#139

Post by whitenoice »

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…

The only problem I have with this is that when you have a lot of joins and your query is long it gets confusing, as in what table was 'l', what table was 'a' and then you have to scroll up and look it up again, whereas in the parent comment its easy as it makes it explicitly clear and the table name does not matter when looking at joins.

Re: Design better databases

#140

Post by jimvgg »

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?

SQL complete does this nicely, although I gather it is a little expensive
Post reply on HN