Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Old, Good Database Design

relinx.io

Post reply on HN
Print view View original

51–60 of 167 posts

Re: Old, Good Database Design

#51

Post by Supermancho »

geebee wrote: post #31

Nice link. Nothing controversial, but sometimes simplicity is controversial in our field. I've slowly come around to seeing proper database design as the most essential foundation of an IT system. I remember reading "your data will outlast your application", and I've been around as a developer long enough to have lived it. One big anti-pattern I've seen with ORMs is that developers who don't really think in terms of…

> Nothing controversial

ahem

> Foreign Key constraint is the king of the relational database design

Amazon does not use FK constraints and I have rarely run into systems that do (since 1996ish). Most people with big enough datasets learn not to use them. The overhead for orphaned data is far less than the consequences of using them.

Re: Old, Good Database Design

#52

Post by pmontra »

gopalv wrote: post #2

> A well-thought design can save us many hours of coding, testing, and troubleshooting. That is the very definition of a waterfall design model. I've turned into a fluid-design advocate over the years, where every design principle follows a next question - "okay, this is good but how would I change it?". So you start with a unique constraint and four months later, you find out that it is not actually unique (like "tw…

Well-thought design saves time even in agile projects. Maybe it's a one day activity instead of three months of it and yet it makes a difference. Basically each activity (or whatever we call it) is a micro waterfall.

Example from today: a developer came back from a week of vacation, listened to the stand up meeting this morning and pointed out that we misunderstood the purpose of a table he worked on time ago. Result: a few hours of last week's coding were useless and we spent a couple of hours together at redesigning the activity. The total impact should be of about one day.

Re: Old, Good Database Design

#53

Post by shoguning »

SamuelAdams wrote: post #40
commandlinefan wrote: post #27

Some people choose nosql alternatives because they've spent time analyzing the performance of a proper relational model and have determined that an RDBMS will generate too much overhead for their data load and consciously accept the tradeoffs involved in giving up automated referential integrity. Most people, though, choose nosql alternatives because they're too lazy to learn how to model data.

I am forever grateful that I took a full semester of database design in my undergrad. This single skill has stood with me for my entire career so far and has enabled me to figure out the root cause of many production issues. Plus people really like it when you can answer ad-hoc questions like "what inspections are still open and when were they first opened". If y'all can understand Angular / React / Vue there's no re…

Pardon my ignorance--is inspections some concept that relates to database management, or are you referring to a query like "select * from inspections where status = 'open';". Honestly asking.

Re: Old, Good Database Design

#54

Post by TheRealSteve »

This seems contrary to what I have learned in my career as an application developer on data heavy platforms. Namely the first section that concludes: > Having stressed the importance of good database design...

I'm not in agreement with the author's concept of good design. I don't want other "doors" to edit the data that bypass the application logic. That's the mess SQL enables for DBAs and scripts that think it's okay to change data without executing business logic.

I think it's better to accept your data and application layers are coupled and plan to evolve and refactor them together rather than teach your database to defend itself from the evil business logic in applications.

I also don't think this dictates relational DBs vs nosql. Your data model is probably relational but how you choose to store and access the data depends more on what use cases you are trying to enable.

Re: Old, Good Database Design

#55

Post by geebee »

Supermancho wrote: post #51
geebee wrote: post #31

Nice link. Nothing controversial, but sometimes simplicity is controversial in our field. I've slowly come around to seeing proper database design as the most essential foundation of an IT system. I remember reading "your data will outlast your application", and I've been around as a developer long enough to have lived it. One big anti-pattern I've seen with ORMs is that developers who don't really think in terms of…

> Nothing controversial ahem > Foreign Key constraint is the king of the relational database design Amazon does not use FK constraints and I have rarely run into systems that do (since 1996ish). Most people with big enough datasets learn not to use them. The overhead for orphaned data is far less than the consequences of using them.

Ah. Fair enough, yes, I agree. That statement goes a little too far, and is a bit at odds with the otherwise overall light tone of the article and the YMMV-ish disclaimer at the end.

Re: Old, Good Database Design

#56

Post by slifin »

Most relational databases aren't relational enough

For example in Drupal you can have a node table which is a foreign key relationship to many other custom tables

In SQL I can't say get me all the nodes that have a start date without explicitly left joining to a potentially dynamic number of other tables using a higher level language to modify the query

In Datomic or Datascript or Datahike or Datalevin or Crux this query would be trivial these are not nosql databases they're EAVT datalog engines, that in some cases also support SQL for compatibility

Re: Old, Good Database Design

#57

Post by crgwbr »

shoguning wrote: post #53
SamuelAdams wrote: post #40

Earlier quoted context omitted.

I am forever grateful that I took a full semester of database design in my undergrad. This single skill has stood with me for my entire career so far and has enabled me to figure out the root cause of many production issues. Plus people really like it when you can answer ad-hoc questions like "what inspections are still open and when were they first opened". If y'all can understand Angular / React / Vue there's no re…

Pardon my ignorance--is inspections some concept that relates to database management, or are you referring to a query like "select * from inspections where status = 'open';". Honestly asking.

I believe OP is just referring to the ability to run adhoc queries. Typically nosql solutions are built to be performt for common access patterns, at the expense of being difficult or impossible to query in unplanned ways. SQL DBs are very good in that regard.

Re: Old, Good Database Design

#58

Post by EvanAnderson »

throw345hn wrote: post #45
SamuelAdams wrote: post #40

Earlier quoted context omitted.

I am forever grateful that I took a full semester of database design in my undergrad. This single skill has stood with me for my entire career so far and has enabled me to figure out the root cause of many production issues. Plus people really like it when you can answer ad-hoc questions like "what inspections are still open and when were they first opened". If y'all can understand Angular / React / Vue there's no re…

Could you suggest resources (books, articles, videos, moocs or others) to learn good database design. I am picking up skills about sql but want to better understand and learn about databases. As someone who doesnt have that background, a lot of the times I am just googling for stuff and just trying out bits and pieces.

Apparently there's a 4th edition coming, but if you like the form-factor of a book-length text I'd throw a recommendation to "Database Design for Mere Mortals"[1]. I read the 1st edition from the late 90's, but I'd imagine it's still just as good. It approaches database modeling from a practical non-technical perspective, and I found it helped me learn data modeling in a software-agnostic manner, and later to influence how I talked to non-technical audiences about data modeling. I'm really glad to have found it early in my career.

I will echo what others have said. Data modeling is a force-multiplier type of skill. Combine it with a reasonable understanding of SQL and you can return a lot of value very quickly.

[1] https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp...

Re: Old, Good Database Design

#59

Post by downvoteme1 »

simonw wrote: post #8

My least favorite part of database design is the bit where you have to pick lengths for your char columns. Twenty years in and I'm still picking these pretty much by guessing. And when I guess wrong it causes really annoying problems further down the line. I love how SQLite doesn't make me do this - it just has a TEXT type which is always unlimited in length.

Why don’t you use varchar(max) as the range always. The varchar data type specified that the length of this attribute is variable in each record and the memory allocated depends only on the number of actual characters stored in the column.

Re: Old, Good Database Design

#60

Post by mcny »

throw345hn wrote: post #45
SamuelAdams wrote: post #40

Earlier quoted context omitted.

I am forever grateful that I took a full semester of database design in my undergrad. This single skill has stood with me for my entire career so far and has enabled me to figure out the root cause of many production issues. Plus people really like it when you can answer ad-hoc questions like "what inspections are still open and when were they first opened". If y'all can understand Angular / React / Vue there's no re…

Could you suggest resources (books, articles, videos, moocs or others) to learn good database design. I am picking up skills about sql but want to better understand and learn about databases. As someone who doesnt have that background, a lot of the times I am just googling for stuff and just trying out bits and pieces.

I'd like to know this as well. I think you'll just have to build things (potentially horribly) and fail.

I took three semesters of database (granted, baby database classes) and I still have no idea how you can do something pretty straightforward like creating a room reservation system.

If there is a reservation beginning at 10:15 AM and ending at 12:30 PM and someone tries to book a reservation from 10:00 AM to 10:30 AM, the transaction should fail.

and before someone screams db2! yes, db2 can. but then you'd have to use db2 https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/co...

Why is this so difficult...

Post reply on HN