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

101–110 of 167 posts

Re: Old, Good Database Design

#101

Post by Noumenon72 »

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…

Is "inspections" a typo for "transactions" here?

Re: Old, Good Database Design

#102

Post by lmm »

alextheparrot wrote: post #20
throwaway894345 wrote: post #16

If one of the purposes of relational databases is data modeling, I've always wondered why there aren't good semantics for sum types. The real world is full of them, but databases can't express them. When I bring this up, some people respond that this is the purpose of ORMs; however, this implies that we have an arbitrary bifurcation in which some of the processing happens efficiently in SQL and anything that depends…

Can you give an example of real world data modeling where you want more expressive sum types over just using enums? Enums are technically a subclass of sum types, but even those are non-trivial to use at a data format level (Try evolving them in an on-the-wire message format like Avro or Protobuf).

Imagine a system that allows third-party login (Facebook / AppleID / whatever) - then the account has either a username/password hash or an oauth token or some other kind of structured data.

Delivery addresses for a system that supports both physical and digital products - you want a type-level distinction between physical and digital addresses, but an order might be being shipped to either.

Subscription vs free trial - they're different kinds of thing, but you want to store more details (e.g. expiry date) than just an enum of one or the other.

Re: Old, Good Database Design

#103

Post by lmm »

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…

> One big anti-pattern I've seen with ORMs is that developers who don't really think in terms of data and relationships use the ORM as a kind of object serialization usable only from the application. Rather than thinking of the database as something useful that could be queried and accessed outside the context of the application, they write objects out to various tables and then re-import and re-construct them once they're needed again in the app, often with dependencies that are in code or even yaml configuration files. The upshot is that you simply can't use the database as anything other than a persistence tier for an app. It really would be no different if they had simply given the objects and id and pickled them to disk. The resulting data store really is that inaccessible and meaningless outside the context of the app.

> As a result, if an analyst wants a report, they can't write SQL to get those reports, even though the persistence tier is, if perhaps in name only, a "relational database". And when the app goes away (as it inevitably does), they'll still want to know, say, how many beakers and test tubes were ordered by a lab tech who participated in 100 or more experiments per year with at least one faculty member from radiology between June and December in 2007.

> But because they don't really know SQL, they see no value to it, and they're honestly just kinda irritated that they don't have an object database, which is what they understand a database to be - a way to pickle and reimport objects. The application outlasts the developer, and the data outlasts the application.

I'm on the developer's side here. Databases are too big and complex to be used by more than one application; if you have a database then it's really important to have a single owner for that database, or you'll never be able to evolve the schema. If you let an analyst write their own queries against your database, you have no control over what queries are running from where or for whom, so you can't so much as rename a column. And sooner or later your production system will lock up because an analyst wrote a query that they didn't realise had too many joins in.

If it's important to have an archival record of your data, put that in your design constraints and build it into the system. Even then, I probably wouldn't pick an SQL database as the system for doing it with.

Re: Old, Good Database Design

#104

Post by dugmartin »

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…

I feel the same way. It’s been nearly 30 years since my undergrad database class and I used what I learned in that class yesterday mocking out a schema for a personal project. We didn’t touch any real dbms in the class but rather did most of the class work using relational algebra.

Re: Old, Good Database Design

#105

Post by 6nf »

DaiPlusPlus wrote: post #79
kccqzy wrote: post #67

Earlier quoted context omitted.

What's difficult about them? I typically use nullable columns and then a check constraint to specify a custom condition for nullability. Columns belonging to the same alternative in the sum type must be all null or all not null. And then there's check only one active alternative.

This approach doesn’t scale when business requirements change regularly such that you need to add or remove columns to an existing table. Adding new columns by creating a new table is easy and cheap and doesn’t involve downtime. Adding 100+ columns to an existing table because the spec said a relationship went from 1:0-1 to 1:1 is a pain. (This can be avoided with creative design with deferrable constraints, somethin…

> Adding 100+ columns to an existing table

Whaaaa 100+ columns? How often do you need to add 100 columns to an existing table?

I mean honestly that has to be as rare as chicken teeth.

Re: Old, Good Database Design

#106

Post by 6nf »

downvoteme1 wrote: post #59
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.

Varchar(max) is fine for some cases but you can't use it for everything because there's some limitations compared to varchar(1000) because the latter is stored in the actual table structure while the former is not.

Re: Old, Good Database Design

#107

Post by jelnur »

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…

> That is the very definition of a waterfall design model.

No it doesn't mean that. When you can rely on your data it's easies to code and test than when you have to account for every bad data combination.

And remove the constraint is easier when you need it, than to add it later.

Thanks for the feedback!

Re: Old, Good Database Design

#109

Post by nine_k »

TheRealSteve wrote: post #54

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

When you see that SQL access can ruin data and make them invalid, it's usually a strong indicator of an inadequate DB design. (Maybe you did not have a chance to see a good one?)

Re: Old, Good Database Design

#110

Post by shoguning »

crgwbr wrote: post #57
shoguning wrote: post #53

Earlier quoted context omitted.

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.

Thanks! And yes, I agree. The ability to write ad hoc SQL is a great skill.
Post reply on HN