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

Things I wished more developers knew about databases

medium.com

Post reply on HN
Print view View original

101–110 of 464 posts

Re: Things I wished more developers knew about databases

#101

Post by carlps »

txcwpalpha wrote: post #73
wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

When I was in undergrad I was part of program that was heavily programming focused, but was actually part of the business school. Several of the classes I took were heavily SQL focused, with at least one class that had every single assignment requiring extensive designing database schemas and writing SQL. Now, years later, I still think that those classes were some of the most valuable to my career as a programmer. W…

I have a very similar background. My degrees are in Business Computer Systems and we had SQL hammered into us. We learned how to program with Java and .NET, but all from a very high level. It wasn't until a couple years into my career after school that I started learning DS+A fundamentals on my own. It blew my mind. I agree that there should be more of a mid-point available.

Re: Things I wished more developers knew about databases

#102

Post by stuaxo »

I just want my databases to be normalised, and my queries optimised.

Stuff like Django CMS is terribly architected, so code is ping-ponging between DB and code.

This would have been a non-issue if all the APIs were built with batching in mind, and the data was normalised in the first place.

Re: Things I wished more developers knew about databases

#103

Post by harikb »

TremendousJudge wrote: post #82
brightball wrote: post #56

Earlier quoted context omitted.

I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it. IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database. Your goal was to accomplish as much as possible in a single…

> The application language was a pass through later between the client and the database. This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with ar…

There is an entire world between ORM and PL/SQL. Programmatically constructing SQL statements is also a thing. Just because someone writes SQL does not mean SQL needs to be spread through out code or that we need to have lot of logic in PL/SQL. Of course, there will be cases where a store procedure is desired (any kind of validation that cannot be expressed as fkeys, canonicalization of some core data components etc). I worked on DBs writing SQL for several years with only minimal code as stored procedures.

Of course the database vendor and culture also plays a role. We were primarily a mysql/postgresql shop

Re: Things I wished more developers knew about databases

#104

Post by jtchang »

stuaxo wrote: post #102

I just want my databases to be normalised, and my queries optimised. Stuff like Django CMS is terribly architected, so code is ping-ponging between DB and code. This would have been a non-issue if all the APIs were built with batching in mind, and the data was normalised in the first place.

Just to be clear you are talking about the CMS and not django itself right? Django itself is fantastic.

Re: Things I wished more developers knew about databases

#105

Post by MaBeuLux88 »

MaxBarraclough wrote: post #67
andybak wrote: post #51

Earlier quoted context omitted.

I disagree. There's more to relationships that typing. I'm very pro-dynamic languages and still chafe at static typing but the wonder of the relational model fits nicely with my liking for declarative and functional approaches. (EDIT - and as another data point I dislike SQL's syntax. The semantics are bearable but the syntax just makes my brain melt)

They're not identical concepts, but both relational schemas and statically typed programming languages provide assurances about basic structure. Mongo, and Python, offer no such assurances, and leave it to the developer to get it right. Agree that SQL's syntax is rather bad.

MongoDB supports Schema Validation since MongoDB 3.2 (Dec. 2015) and JSON Schema since MongoDB 3.6 (Nov. 2017) so MongoDB can enforce types strictly if you want to at the collection level. NoSQL doesn't mean NoSchema or NoTypes. https://docs.mongodb.com/manual/core/schema-validation/#json...

Re: Things I wished more developers knew about databases

#106

Post by JamesSwift »

pbowyer wrote: post #68
JamesSwift wrote: post #59

Earlier quoted context omitted.

The answer is likely that your storage schema is incorrect. You have things stored in OLTP (i.e. app database) but trying to read it as OLAP (i.e. reporting database). Once you reimagine the data in the OLAP style then these kinds of queries are simplistic. EDIT: specifically for your example, in an OLAP style you would generate a Times table and then foreign key the sales table to it based on the date. Then you can…

Yes, agreed. It was designed for OLTP not OLAP, and I have to get my mindset into that. For days as my example has, would the Time table be generated for say 1970-2050? Given months are of different lengths and there are leap years, I'm assuming this is needed, rather than storing a single year.

Yes, the time table is a pregenerated set of all possible moments in time for whatever resolution you care about. So, for example, if you cared about day resolution it might be

  date_key | year_num | month_num | day_num | quarter_num | week_num | month_name | month_name_short
  ...
  20200101   2020       1           1         1             1          january      jan
  20200102   2020       1           2         1             1          january      jan
  ...
  20200401   2020       4           1         2             14         april        apr
  ...
You would FK on the `date_key` and add as many columns as you need to support querying against the dimension. I also like to add a proper `datetime` representation of the date so I can easily do a date range query.

Re: Things I wished more developers knew about databases

#107

Post by fernandotakai »

pjc50 wrote: post #48
wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

This is partly why I love LINQ: it's a more C# flavoured way of expressing queries. And it works on objects as well as databases. Most ORMs are bad for queries (pull over all the objects and look at their properties!), but LINQ will actually turn your code into SQL under the hood with some remarkable machinery.

>Most ORMs are bad for queries (pull over all the objects and look at their properties!)

i would change that to ORMs CAN be bad for queries. most ORMs are super configurable and can be changed only to pull specific stuff.

i would argue that most ORMs are a plus to productivity because for most of cases, you just need simple querying (select * from, simple updates, simple deletes).

also, if you really need the power of raw sql you an just use that -- and even then, for most advanced ORMs (django's, for example) exposes a LOT of really advanced sql stuff in python, which, for a lot of developers, is a lot more expressive.

Re: Things I wished more developers knew about databases

#108

Post by gtsteve »

jakearmitage wrote: post #61
btilly wrote: post #49

Earlier quoted context omitted.

If you're writing a CRUD application, an ORM saves a lot of headaches. If you're doing complex reporting queries, an ORM is strictly worse. And yes, I've seen developers, architects, and authors of ORMs that believed otherwise. They are wrong. As an example, very, very few ORMs can make the distinction between SELECT ... FROM foo LEFT JOIN bar ON foo.id = bar.foo_id AND bar.category_id = 5 LEFT JOIN baz ON bar.id = b…

Curious, what is the difference here?

I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs.

If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it.

This illustrates the subtle problem that ORMs can introduce - all logical tests pass but issues emerge over time. I found that typically every ORM based problem was resolved by rewriting it using SQL.

I used to use ORMs extensively, but my philosophy now is that you can make simple queries by using a query generator (i.e. connection.GetByID("MyTable", 100)) and complex queries have to be written by hand. I would not choose to use an ORM again.

Re: Things I wished more developers knew about databases

#109

Post by hobs »

falcolas wrote: post #60
hobs wrote: post #17

Earlier quoted context omitted.

Observeability, reliability, scalability - really easy to bolt on once you are successful. There's a middle ground, and a DBA turned SRE turned Architect is probably ok with compromises, those are all roles where "it depends" is a bylaw.

So, they really aren't that easy to bolt on, if not considered from the beginning. Monoliths, for example, are a real PITA to make reliable and scalable. Worse, once your company is successful, there will be an endless list of features to add to your product, meaning nobody has the time to "bolt those features on". How the product begins, is how the product often lives on well past it's expected lifespan.

My apologies that the sarcasm was a bit too subtle - no they are not.

Re: Things I wished more developers knew about databases

#110

Post by rurp »

Ididntdothis wrote: post #54
wenc wrote: post #16

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…

SQL feels to me like bash or regular expressions. You can do amazing things if you do it full time. But if you do it only a few times per month or year you quickly forget all the subtleties and it gets hard to understand even the stuff you wrote half a year ago. I guess in the end things have become so complex that as a dev you can’t be good at everything. I often wish there were dedicated database guys but if you ha…

This echos my experience. I've written some complicated SQL queries, but I do it rarely enough that I always have to re-learn a lot of it.

There have been a number of instances where I have had to look something up seemingly for the first time, found a good Stack Overflow answer, and then chuckled to myself because I had already upvoted that exact answer at some point in the past. SQL queries are a pretty common source of this, along with uncommon git and terminal commands.

Post reply on HN