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

Things to know about databases

architecturenotes.co

Post reply on HN
Print view View original

241–247 of 247 posts

Re: Things to know about databases

#241

Post by citrin_ru »

galaxyLogic wrote: post #238
citrin_ru wrote: post #215

Earlier quoted context omitted.

Even if you think you use database-agnostic SQL unless you actually run tests with multiple DBMS you cannot be sure. One can write code which depends on some DB specific behavior unintentionally, Hyrum's Law [1] works for databases too. [1] https://www.hyrumslaw.com/

Exactly. Isn't it a sad state of affairs that you can not write plain pure standards-compliant SQL and be done with it? It's a bit like if you wrote C and could only compile your program with Oracle's C-compiler. Where is progress in SQL standardization going?

Its not only about standards, but also about tools to check that you are actually following them. It is easy to write C code which will compile with one compiler but not with another. That's why if compatibility is desired it is better to compile code with different compilers in CI.

In theory we can use a tool which tells if you SQL is ANSI compliant, but this would not tell if your code expect only standard compliant behavior from the database. E. g. some RDBMs are flexible in which formats they accept datetime strings and some are not. SQL can be standards-compliant but code still can fail with one DB and work with another.

It is not to say that standards are useless. It much easier to port an application from one DB to another if a developer was trying to use only ANSI SQL. Or you can write an application which can work with multiple RDBMs without modification but like with almost everything else - if you haven't tested it you cannot be sure that it works.

Re: Things to know about databases

#242

Post by Akronymus »

SulphurSmell wrote: post #61

This article is informative. I have found that databases in general tend to be less sexy than the front-end apps...especially with the recent cohort of devs. As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. This is especially true the bigger it gets, and the longer it stays in production. That said, if you are influencing the d…

I personally try to strive towards a database design as if the next person were to know my address and having anger and control issues.

Fixing up a database step by step is a painful process.

Re: Things to know about databases

#243

Post by Akronymus »

Aeolun wrote: post #175
mmcnl wrote: post #130

Earlier quoted context omitted.

"Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious." -- Fred Brooks, The Mythical Man Month (1975)

This man has clearly never seen our database schema. Show me either flowcharts and/or tables, it doesn’t matter, I’ll continue to be mystified.

Hey, at least you don't store gender in the address table, where one person can have multiple addresses.

https://news.ycombinator.com/item?id=27842820

Re: Things to know about databases

#244

Post by mmcnl »

SulphurSmell wrote: post #162
mmcnl wrote: post #130

Earlier quoted context omitted.

"Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious." -- Fred Brooks, The Mythical Man Month (1975)

This is going on my wall. Thanks so much.

I got from a comment on HN a few months ago, so also thank the commenter that I don't remember.

Re: Things to know about databases

#245

Post by Akronymus »

randomdata wrote: post #60
tiffanyh wrote: post #23

#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.

> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work…

>Except the most important problem: A pleasant API.

For that, there are stored procedures.

Re: Things to know about databases

#246

Post by Akronymus »

SkeuomorphicBee wrote: post #219
brightball wrote: post #91

Earlier quoted context omitted.

I've honestly never understood why people have such a distaste for SQL. SQL and Linux/Unix have been the biggest constants of my entire programming career to this point (20ish years). I always know I can count on them.

SQL is also a big constant of my programming career, I know it can do everything I need, but sometimes it is frustrating how its limitations make some simple things complicated. For example, one of the biggest gripes I have with SQL is the fact that the table is the only output format, it doesn't allow outputting data with different cardinalities in a single query. The internal relational model of a RDBMS is extremel…

> For example, one of the biggest gripes I have with SQL is the fact that the table is the only output format, it doesn't allow outputting data with different cardinalities in a single query.

With MSSQL at least you can return multiple result sets. Not sure about other database vendors.

Re: Things to know about databases

#247

Post by tomnipotent »

eurasiantiger wrote: post #233
tomnipotent wrote: post #200

Earlier quoted context omitted.

With just a splash of row_to_json and json_agg, you can JSON encode your entire query in PG. SELECT json_agg(row_to_json(t)) FROM information_schema.tables as t;

The dealbreaker here is having to do this dance for every nested field on every entity, and having to write a separate backend query for each separate front-end use-case for a single entity. It just isn’t feasible to write everything out when the schema has >30 entities, some with N:M relations, and when queries routinely go several levels deep to fetch dozens of joined fields at every level. The boilerplate overhead…

Hasura, a GraphQL server, uses exactly this technique to transform final query results into JSON in PG before bringing back into local memory.
Post reply on HN