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

191–200 of 247 posts

Re: Things to know about databases

#191

Post by vbezhenar »

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 agree. I have some kind of design hierarchy. Database -> Architecture -> Services for outside consumers -> Backend -> Frontend. Things coming first must be designed more thoroughly as they're likely to live longer. Proper database design is paramount. Spend as much time as necessary. Iterate before going live as long as necessary to ensure that design is sound. Because it's so much harder to change database later. Trivial changes often require huge efforts.

Re: Things to know about databases

#192

Post by bitexploder »

vbezhenar wrote: post #191
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 agree. I have some kind of design hierarchy. Database -> Architecture -> Services for outside consumers -> Backend -> Frontend. Things coming first must be designed more thoroughly as they're likely to live longer. Proper database design is paramount. Spend as much time as necessary. Iterate before going live as long as necessary to ensure that design is sound. Because it's so much harder to change database later.…

Rob Pike’s 5 rules:

https://users.ece.utexas.edu/~adnan/pike.html

Rule 5. Data dominates. If you've chosen the right data structures and organized things well, the algorithms will almost always be self-evident. Data structures, not algorithms, are central to programming.

Re: Things to know about databases

#193

Post by oarabbus_ »

IgorPartola wrote: post #187
oarabbus_ wrote: post #133

Earlier quoted context omitted.

Honestly, I think it's because a lot of folks go their entire careers avoiding set-based thinking. Many of these folks are talented programmers so I would assume they'd become quite decent at SQL if they devoted time to it. I'm speaking more on the DML side than the DDL side here. In my experience The rest range from "SELECT * is as far as I'll go; where's the ORM" to "why the hell are you using cursors to aggregate"…

The reason isn’t set based thinking avoidance. I think it is because fundamentally we want to work with smart objects and not rows of data in most cases.

More often than not that's a good example of set-based thinking avoidance. The application graveyard is full of projects that felt their transactional data was too sophisticated for "dumb rows" and reinvented an inefficient relational model. I'd love to hear more about these "smart objects" - they're usually not that smart and typically can be represented relationally.

Even for use-cases like graph based models you still find Twitter and Facebook using MySQL to build the graph on top of. It’s simply heavily abstracted for the majority of engineers at those companies (where I'd wager the <20% proficiency in set-based thinking holds true) but it still fundamentally relies on SQL.

Re: Things to know about databases

#194

Post by galaxyLogic »

kccqzy wrote: post #184
SulphurSmell wrote: post #93

Earlier quoted context omitted.

>business logic is put into stored procedures This is a double-edged sword. I have seen massive business logic baked into stored procedures...so much so, that the applications themselves are rather slim. If this stored procedure code is properly versioned and otherwise managed, this is not entirely bad. If the data model is sound, I don't worry that much...stored procs vs 100KLOC of Java code? I can tell you what is…

A colleague of mine has a very strong opinion that any candidate who mentions using stored procedure in an interview without immediately disparaging it is an immediate no-hire. I sometimes wonder what kind of experience formed that opinion. (I personally never worked at a place with significant use of stored procedures.)

The basic idea is the same as in the article: Data is more important that apps and stored procedures really are just app-code.

Stored procedures have the downside that they often work only in one vendor's database.

Re: Things to know about databases

#196

Post by tomnipotent »

galaxyLogic wrote: post #194
kccqzy wrote: post #184

Earlier quoted context omitted.

A colleague of mine has a very strong opinion that any candidate who mentions using stored procedure in an interview without immediately disparaging it is an immediate no-hire. I sometimes wonder what kind of experience formed that opinion. (I personally never worked at a place with significant use of stored procedures.)

The basic idea is the same as in the article: Data is more important that apps and stored procedures really are just app-code. Stored procedures have the downside that they often work only in one vendor's database.

> Stored procedures have the downside that they often work only in one vendor's database.

Doesn't really matter, because no one is writing database-agnostic SQL (unless that's part of your product). Any non-trivial implementation is going to require the use of proprietary SQL.

Re: Things to know about databases

#197

Post by ungamedplayer »

layer8 wrote: post #185
Jemaclus wrote: post #173

Earlier quoted context omitted.

> And, if you can, please, limit the use of anything that can hold a NULL. I'm curious: what's the alternative to NULL? I'm struggling to think of a database where NULL wouldn't be super useful. It feels like NULL as a concept is almost required, but I think you're suggesting that's a faulty assumption. Would love to hear more about this.

See here: https://stackoverflow.com/a/4358687

not op, but ty.

Re: Things to know about databases

#198

Post by aahortwwy »

omginternets wrote: post #142
oarabbus_ wrote: post #133

Earlier quoted context omitted.

Honestly, I think it's because a lot of folks go their entire careers avoiding set-based thinking. Many of these folks are talented programmers so I would assume they'd become quite decent at SQL if they devoted time to it. I'm speaking more on the DML side than the DDL side here. In my experience The rest range from "SELECT * is as far as I'll go; where's the ORM" to "why the hell are you using cursors to aggregate"…

Where can I learn about the relationship between set-theory and SQL?

Discrete Mathematics with Applications by Epp. Chapters 5, 7, 10.

Fundamentals of Database Systems by Elmasri & Navathe. Part 2.

Re: Things to know about databases

#199

Post by zvrba »

brightball wrote: post #91
randomdata wrote: post #60

Earlier quoted context omitted.

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

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.

I love the data model of RDBMS / SQL. I hate SQL _the language_. Verbose, irregular, attempting to mimic English and thus making it unintuitive and next to impossible to remember. 80% of my lookups into reference documentation is about syntax for things I don't use that often.

Examples: GRANT TO, REVOKE FROM, DENY TO (oh yes, what's the difference between REVOKE and DENY? and did you know that you can REVOKE a DENY?), arbitrary requirements for punctuation (e.g., = in BACKUP DATABASE AdventureWorks2012 TO DISK = 'X:\SQLServerBackups\AdventureWorks1.bak'), dubious context-sensitive "magical identifiers" (e.g., SELECT DATEPART(year, @t)), non-composability (how do you dynamically give a part specification to DATEPART?), etc, etc, etc.

It's possible to write a novel about just how unstructured and irregular "structured" query language is.

Re: Things to know about databases

#200

Post by tomnipotent »

TheaomBen wrote: post #135
ElectricalUnion wrote: post #128

Earlier quoted context omitted.

> Can you write an SQL query to return arbitrary JSON? Returning queried data in a nested form is a must nowadays. Yes. https://www.sqlite.org/json1.html

Also https://www.postgresql.org/docs/current/functions-json.html

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;
Post reply on HN