Things I wished more developers knew about databases • Hacker News Board

Re: Things I wished more developers knew about databases

#71

Post by will_pseudonym » Wed, Apr 22, 2020, 3:48 PM UTC

EvanAnderson wrote: post #62

pbowyer wrote: post #45

Earlier quoted context omitted.

I like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need. When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the…

Your example is a fun one and not too hard to do. Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets. In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sale…

I learned SQL working in Access. There wasn't anything like generate_series(), and I remember having to do things like above by having a start and end date, cross joined with a table of nonnegative integers I made, and creating a field which added an integer to the start date, subject to being no greater than the end date.

Re: Things I wished more developers knew about databases

#72

Post by EvanAnderson » Wed, Apr 22, 2020, 3:50 PM UTC

pbowyer wrote: post #66

EvanAnderson wrote: post #62

Earlier quoted context omitted.

Your example is a fun one and not too hard to do. Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets. In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sale…

> Set 1 could be a "numbers table" or generated with something like "generate_series()" I've done it this way when doing it per-minute or per-hour - i.e. for 60 minutes or 24 hours; fixed, constant ranges. I picked this example because (to me at least) it's more difficult :) Calendar months have different lengths, so something would have to tell it which month to use (and account for leap years). But say you wanted t…

In many databases you would create a function to fill the role of that "numbers table" and, effectively, map that function onto rows. That function can express whatever "richness" you need (i.e. civil calendar month, phase of the moon, etc... >smile<) that might not be easily expressed as a series.

Re: Things I wished more developers knew about databases

#73

Post by txcwpalpha » Wed, Apr 22, 2020, 3:50 PM UTC

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.

What's interesting to me is that apparently the CS program at my university hardly did anything with SQL, and I notice too that most programmers I meet "in the wild" are lacking in SQL skills, as you mentioned. It's led to some interesting interview situations where I really struggle with any questions about algorithms (my college courses didn't cover algorithms at all) while the interviewer will tell me that I have the best SQL skills of anyone they interviewed.

Speaking with others that went to other universities, I've heard that it's similar elsewhere for the "business/programming" to include SQL classes but eschew algorithms, while CS programs will ignore SQL but focus heavily on algorithms. It seems to me like both programs could benefit from meeting in the middle a bit.

Re: Things I wished more developers knew about databases

#74

Post by uhoh-itsmaciek » Wed, Apr 22, 2020, 3:51 PM UTC

LoSboccacc wrote: post #37

stronglikedan wrote: post #19

Earlier quoted context omitted.

I'd posit that autoincrements are fine for primary keys, but primary keys aren't fine for auditing.

how would you generate hole-free sequences for use cases like that of parents (invoicing)?

Triggers are a great option in many situations.

Re: Things I wished more developers knew about databases

#75

Post by balls187 » Wed, Apr 22, 2020, 3:51 PM UTC

falcolas wrote: post #7

(The 80/20 rule applies below, some developers do care) Developers... just don't care. They want to spin up an ORM, point it at a URI, and forget about it. I've fought this for over a decade now as a DBA, SRE, DevOps, and architect. Most of the developers don't want to deal with anything infrastructure-wise; they want to spend all the time they can just focusing on the problem they're writing software to solve. Obser…

Probably because they don't need to think about those things.

I started my career as an Embedded Software Engineer, and memory allocation, and clock cycles hand to be managed. Our software ran on systems with limited memory, and had to fit in one 60hz cycle. We supported VAX system that used VAX Floating point, and had to be cognizant of both floating point conversions AND endian byte encoding.

These days, such concepts are basically just trivia answers for interview questions.

I had to think about those things because it was required. System software would crash, and debugging it on an expensive government flight sim 1000 miles away was impossible.

Perhaps for the developers you work with, they don't need to think about those things because they're not required to. After all they have access to a dedicated DBA/SRE/DevOps/Architect guru.

Re: Things I wished more developers knew about databases

#76

Post by will_pseudonym » Wed, Apr 22, 2020, 3:53 PM UTC

pbowyer wrote: post #66

EvanAnderson wrote: post #62

Earlier quoted context omitted.

Your example is a fun one and not too hard to do. Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets. In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sale…

> Set 1 could be a "numbers table" or generated with something like "generate_series()" I've done it this way when doing it per-minute or per-hour - i.e. for 60 minutes or 24 hours; fixed, constant ranges. I picked this example because (to me at least) it's more difficult :) Calendar months have different lengths, so something would have to tell it which month to use (and account for leap years). But say you wanted t…

There are date functions in most SQL packages which can create the limiting factors you're looking for, just as in procedural languages. Once you have the list of dates you're interested in, the problem becomes simple.

Re: Things I wished more developers knew about databases

#77

Post by Amezarak » Wed, Apr 22, 2020, 3:54 PM UTC

pbowyer wrote: post #66

EvanAnderson wrote: post #62

Earlier quoted context omitted.

Your example is a fun one and not too hard to do. Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets. In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sale…

> Set 1 could be a "numbers table" or generated with something like "generate_series()" I've done it this way when doing it per-minute or per-hour - i.e. for 60 minutes or 24 hours; fixed, constant ranges. I picked this example because (to me at least) it's more difficult :) Calendar months have different lengths, so something would have to tell it which month to use (and account for leap years). But say you wanted t…

I have done this many times. My preferred solution was to create a recursive CTE beginning with the desired start date and ending with the desired end date. I often based these on MIN and MAX subqueries, but obviously you could also pick arbitrary dates.

You can use days, months, weeks, years, or whatever you wanted as the "increment" in the CTE using the DATEADD function. Then (for nulls) you simply LEFT JOIN your CTE with the desired date part (aggregated) of your table. This gives you your first answer, and a simply filter will get you your second answer.

It's also common for people to just create all these date tables beforehand as actual, materialized tables. In my opinion, this is less elegant (what happens in 2101?! Somebody had better remember to add to the table!) but it naturally works just as well and probably saves some perf.

Re: Things I wished more developers knew about databases

#78

Post by yjftsjthsd-h » Wed, Apr 22, 2020, 3:55 PM UTC

lowercased wrote: post #43

bdamm wrote: post #34

Earlier quoted context omitted.

I wish I could slap anyone who gives a hoot about tabs vs spaces. Fortunately modern languages like go are removing the version control problem that not caring about style and using auto-formatting IDEs produces.

I'm working in a couple of projects where there's a bunch of linter-checker things that prevent any PR merges (another... imo somewhat over-used tool) and... I split my time between Java, PHP, various SQL engines and various JS frameworks (react, extjs, vue, etc) and I'm constantly battling different mental models with various IDEs always showing different colored squiggles and highlights telling me all the ways I'm…

That sounds like bad tooling. Why not have every project use https://editorconfig.org/ and then have your IDE auto-format? It shouldn't be popping up and making you fix it, it should fix it for you.

Re: Things I wished more developers knew about databases

#79

Post by balls187 » Wed, Apr 22, 2020, 3:55 PM UTC

LoSboccacc wrote: post #37

stronglikedan wrote: post #19

Earlier quoted context omitted.

I'd posit that autoincrements are fine for primary keys, but primary keys aren't fine for auditing.

how would you generate hole-free sequences for use cases like that of parents (invoicing)?

If it was a business requirement that you have perfectly sequential invoice numbers with no gaps, do it at the application level, not at the storage level.

Let the database do what it's great at doing: efficiently store and retrieve data.

Re: Things I wished more developers knew about databases

#80

Post by alexilliamson » Wed, Apr 22, 2020, 3:57 PM UTC

pbowyer wrote: post #45

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…

I like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need. When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the…

You can (IMO) pretty succinctly enumerate all the days in a month with a recursive CTE. The example in the following link does it for weekdays, but it's the same idea: https://www.sqlservertutorial.net/sql-server-basics/sql-serv...