Antigravitas posted:

I'm an old square and design my database schema first before even thinking about the application that may use it.

In the majority of use cases it's the data you are interested in. If you can nail down what your data looks like and how to test it for correctness, you can rewrite or throw away your entire program code and reuse the DB without worry. Or have two separate programs run by different buffoons connect to the same DB without blowing up.

It's like unit testing but for data, if some young kid needs convincing.

Ruggan posted:

Yeah this.

And sorry, I probably misspoke in my sleep induced stupor. When I was talking about "business logic" in my post earlier, I was more referring to the logic that determines certain properties of objects (e.g. "You are not using this feature but because you are an international customer, it isn't relevant to you so we will show N/A instead."). I was not talking about the business rules that form decision points in the application (e.g. "You are returning this item and because it has been 90 days since your purchase, you are only going to receive store credit.").

Business logic is really a vague term that encompasses a wide array of stuff depending on who you ask (validation, calculation, process flow, decision making). I agree that a lot of that belongs on the server and front-end, not the database. The problem (in my opinion) starts when an application shows things to the end user that are derived from rows in a database and can only be reproduced by running them through some complex server-side algorithm.

TheFluff posted:

orm's are bad

query builders (sql metaprogramming tools, e.g. jooq, sqlalchemy, knex) are good

Munkeymon posted:

It's been a while since I used sqlalchemy but I sure had it lumped in with ORMs in my head?

Data Graham posted:

Also I feel like a lot of this is at odds with the whole separation-of-concerns concept, isn't it? Build your DB and your API in a way that's agnostic to how it's going to be used or what kind of app will be built around it; if it's a piece of data extraction logic that everybody will need, by all means implement it in the DB, but otherwise it's up to each app developer to fetch the primitive data objects and mash them together however they see fit. Right? It's not as fast, to execute or to develop or to bug-fix, but speed was never the goal of separation-of-concerns in the first place.

ElehemEare posted:

This falls apart when teamA and teamB decide there are two distinct mechanisms to mash together the data intending to produce the same value, but doing so differently with slightly different results.

Spikes32 posted:

basic

D34THROW posted:

People still use BASIC? I thought that was the domain of hobbyists by this point?

Antigravitas posted:

Once you've put a lot of institutional knowledge into code it becomes impossible to switch away from the language the code is written in without introducing problems because, and this is especially true if you no longer have people around who are familiar with it, quirks or bugs in the code become impossible to distinguish from intentional quirks in the code.

This is especially true when the code is dealing with some real life thing, because real life has the nasty habit of being insufficiently pure and not mapping well to code.

That's how you get BASIC in a "modern" application. It happens all the time and I haven't seen an organisation able to prevent it.

Antigravitas posted:

That's how you get BASIC in a "modern" application. It happens all the time and I haven't seen an organisation able to prevent it.

nem posted:

Obligatory https://www.youtube.com/watch?v=b2F-DItXtZs

I'm firmly in the Postgres camp. Native support for JSONB and efficient time-series storage with Timescale make life so much easier.

Paper With Lines posted:

How do you hook up time series db stuff to a front end without making an aggregation choice?

Paper With Lines posted:

I work a lot with SCATA systems (like factory systems) and I am basically one of five or six people that know how to effectively grab data out of the historian. All the business oriented people want it in PowerBI or Tableau and want to do drill down poo poo, but it is effectively impossible if I just pass through different queries with different aggregations.

ElehemEare posted:

Your UI just needs to account for the fact that a single record is in fact many potential permutations of a record.

I haven’t touched TimescaleDB, but the MSSQL implementation of system-versioned tables just lets you pass “FOR SYSTEM_TIME ALL” against a temporal table, which dumps the full history of mutation of a record.

Paper With Lines posted:

How do you hook up time series db stuff to a front end without making an aggregation choice?

I work a lot with SCATA systems (like factory systems) and I am basically one of five or six people that know how to effectively grab data out of the historian. All the business oriented people want it in PowerBI or Tableau and want to do drill down poo poo, but it is effectively impossible if I just pass through different queries with different aggregations.

D34THROW posted:

I wish I could get my company to host a SQL server. Our ERP is SQL-based (I think) but it sucks rear end (Dynamics NAV 2013), and it doesn't do half of what we really need it to do.

Paper With Lines posted:

The loving Navision schema set up is LOL poo poo. How did you reverse engineer it? We basically have 5 general queries that we adapt depending on what we want that were written like six years ago because no one ever wants to ever interact with it on the db side but there is a high business demand to get data out.

Anveo posted:

I'm trying to make an analytic query that counts events by month. The following query works but takes a while and I feel like there is a better solution with a pivot or crosstab or something but can't seem to get my head around the usage.

code:

SELECT 
    table1.f1,
    table2.f2,
    ...
    CASE WHEN table1.created_at < '2020-02-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2020-01-01 00:00:00' AND '2020-02-01 00:00:00') ELSE NULL END as "2020-01",
    CASE WHEN table1.created_at < '2020-01-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-12-01 00:00:00' AND '2020-01-01 00:00:00') ELSE NULL END as "2019-12",
    CASE WHEN table1.created_at < '2019-12-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-11-01 00:00:00' AND '2019-12-01 00:00:00') ELSE NULL END as "2019-11",
    CASE WHEN table1.created_at < '2019-11-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-10-01 00:00:00' AND '2019-11-01 00:00:00') ELSE NULL END as "2019-10",
    CASE WHEN table1.created_at < '2019-10-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-09-01 00:00:00' AND '2019-10-01 00:00:00') ELSE NULL END as "2019-09",
    CASE WHEN table1.created_at < '2019-09-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-08-01 00:00:00' AND '2019-09-01 00:00:00') ELSE NULL END as "2019-08",
    CASE WHEN table1.created_at < '2019-07-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-06-01 00:00:00' AND '2019-07-01 00:00:00') ELSE NULL END as "2019-06",
    CASE WHEN table1.created_at < '2019-06-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-05-01 00:00:00' AND '2019-06-01 00:00:00') ELSE NULL END as "2019-05",
    CASE WHEN table1.created_at < '2019-08-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-07-01 00:00:00' AND '2019-08-01 00:00:00') ELSE NULL END as "2019-07",
    CASE WHEN table1.created_at < '2019-05-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-04-01 00:00:00' AND '2019-05-01 00:00:00') ELSE NULL END as "2019-04",
    CASE WHEN table1.created_at < '2019-04-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-03-01 00:00:00' AND '2019-04-01 00:00:00') ELSE NULL END as "2019-03",
    CASE WHEN table1.created_at < '2019-03-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-02-01 00:00:00' AND '2019-03-01 00:00:00') ELSE NULL END as "2019-02",
    CASE WHEN table1.created_at < '2019-02-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2019-01-01 00:00:00' AND '2019-02-01 00:00:00') ELSE NULL END as "2019-01"
    ,
    CASE WHEN table1.created_at < '2018-01-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-12-01 00:00:00' AND '2019-01-01 00:00:00') ELSE NULL END as "2018-12",
    CASE WHEN table1.created_at < '2018-12-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-11-01 00:00:00' AND '2018-12-01 00:00:00') ELSE NULL END as "2018-11",
    CASE WHEN table1.created_at < '2018-11-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-10-01 00:00:00' AND '2018-11-01 00:00:00') ELSE NULL END as "2018-10",
    CASE WHEN table1.created_at < '2018-10-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-09-01 00:00:00' AND '2018-10-01 00:00:00') ELSE NULL END as "2018-09",
    CASE WHEN table1.created_at < '2018-09-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-08-01 00:00:00' AND '2018-09-01 00:00:00') ELSE NULL END as "2018-08",
    CASE WHEN table1.created_at < '2018-08-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-07-01 00:00:00' AND '2018-08-01 00:00:00') ELSE NULL END as "2018-07",
    CASE WHEN table1.created_at < '2018-07-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-06-01 00:00:00' AND '2018-07-01 00:00:00') ELSE NULL END as "2018-06",
    CASE WHEN table1.created_at < '2018-06-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-05-01 00:00:00' AND '2018-06-01 00:00:00') ELSE NULL END as "2018-05",
    CASE WHEN table1.created_at < '2018-05-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-04-01 00:00:00' AND '2018-05-01 00:00:00') ELSE NULL END as "2018-04",
    CASE WHEN table1.created_at < '2018-04-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-03-01 00:00:00' AND '2018-04-01 00:00:00') ELSE NULL END as "2018-03",
    CASE WHEN table1.created_at < '2018-03-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-02-01 00:00:00' AND '2018-03-01 00:00:00') ELSE NULL END as "2018-02",
    CASE WHEN table1.created_at < '2018-02-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2018-01-01 00:00:00' AND '2018-02-01 00:00:00') ELSE NULL END as "2018-01"
    ,
    CASE WHEN table1.created_at < '2017-01-01 00:00:00' THEN COUNT(table2.*) FILTER (WHERE table2.sent_at BETWEEN '2017-12-01 00:00:00' AND '2019-01-01 00:00:00') ELSE NULL END as "2017-12",
    ... -- more months
FROM ...

Nth Doctor posted:

oh my god.


Yeah you want to pivot based on the month and year of table1.created_at

Ruggan posted:

That is a loving sexy query

PhantomOfTheCopier posted:

I doubt that does

Paper With Lines posted: