The incoming CEO who wanted to make his mark (let's call him Derek) had an impressive background in marketing decided that the right thing to do was to rationalise everything into one huge database.

They employed a small army of consultants who covered the entire office (it was a big office) in huge database schema diagrams. This took several months. Eventually they "discovered" that the only data that the schemas had in common was a reference, a description, and a created date field.

So they immediately cancelled the project and we all breathed a sigh of relief. Haha, only kidding. Of course they didn't. The right solution was to go schemaless. Enter mongodb. They would achieve full flexibility going forward, and wouldn't have to subside a small diagramatic wallpapering industry as a bonus.

At this point I was no longer involved and just watched the slow train wreck from afar. Years later all the applications had finally been migrated to use the new OneDatabaseToRuleThemAll™ system. I don't think any new functionality was delivered over that period. There were persistent performance issues for the larger data sets. Changing anything required the code to support all previous possible schemas because they never migrated any data (it's schemaless, it's all super flexible!).

I think Derek left to go ruin somewhere else, with a huge success story on his CV.

Secondly, yes, Derek left for another job immediately afterwards. And he came to us quite highly recommended.

I think i encountered "schema-on-read" vs "schema-on-write" from the book designing data-intensive applications

It is so frustrating when people inevitably converge on an obvious schema that they just don't want to let the database know about - or develop the Things table that re-implements database schemas poorly. The old table that is clearly several other tables mashed together.

This data is going to be the longest lived and probably most valuable artefact of the company. It is nearly free to put a schema on it, it improves correctness and performance more or less for free. Yet still it isn't possible to get consensus that telling the database the data schema might help.

People don't realise how inefficient JSON blobs are either. I swear there must be AWS databases losing a significant fraction of their IO budget to re-parsing what should be column names with every data point read. I can only hope that postgres devs have rolled their eyes and optimised the case where people don't know how to express tables in SQL.

and

> I do not understand how they are both smarter than me in many respects, and then still don't understand how stupid this all is.

This is such a good question. The absolute worst codebases I've worked on have been created by brilliant individuals. I remember spending months tracking down bugs leading to inconsistencies in the universal "Things" table. "Everything is a thing! Right?!" Wrong...

The huge application consisted of a single PHP file. If you guessed that it was tens of thousands of lines long, you'd be wrong. It was only a few hundred lines, if not less!

The trick was storing code in the freaking database. The mother of all index.php's would simply query for the correct piece of code based on query parameters and eval it! They had an editor where you could make live code changes to the page, and even some rudimentary version control. Amazing times!

If someone is tasked to port a similar application to the web, writing a PHP application that does the same is not a bad idea.

+1 to this. Worked on a codebase where the (lone, unsupervised, I'm assuming) developer had created their own ORM and object model. Impressive! Except neither had been updated in the (5+, IIRC) years since the original dev left because they were hideously complex and fragile and thus increasingly a drag on getting anything done (which is why management were Very Keen on a total rewrite in A.N.Other language.)

"Everything is a thing" works well in RAM; we have nicely working programming languages based on this. What could go wrong if everything is just a thing in the durable storage?

I’ve started to think of this as a diffuse schema. I.e. the schema never really goes away in “schemaless” databases. It just spreads throughout the application in helper functions, mappings, and backward compatibility hacks.

Perhaps the distinction is between diffuse and consolidated or implicit vs. explicit schema. Are there any similar models or articles that have further described this realization?

If the incentive structure is set up in such a way as to provide an easy local maximal for the individual that is not a good outcome for the compoany, then it is a management failure.

Having such a situation where all individuals up the management chain are doing short-term local maximization that medium term leads to a bad outcome for everyone is societal failure.

I think there are some (many?) people that willfully obfuscate - I have been this person when dealing with narcissists or saving management from themselves - but I just don't see a version of this where he was a very savvy operator.

To quote Patrick McKenzie, the correct calibration is that some amount of evil exists in the world, but the whole world isn't evil. I.e, this is Incompetent Derek, and at some other company is Evil Derek.

I think as an industry we should stop warning juniors of 'premature optimisation' (kids aren't even choosing the right data-structures/algos/architectures and are getting terrible perf), and instead warn them away from premature scalability and premature 'flexibility'.

But there are cases where you need flexibility, and the very categorical dismissal of EAV and anything similar is not particularly helpful if you find yourself in the situation where you need that kind of feature. It's a lot better today with good JSON support in relational databases, but even that doesn't give you good index support unless you give up on some of the flexibility. EAV is actually superior in that aspect if you don't put all your values into a string column.

There's a saying I heard a while ago which I really like, which is "reverse all advice". Almost all advice, such as "be more careful about what you're eating" could be good for one person and bad for another. In our industry, I see EAV applied stupidly far more often than I see it used appropriately. In fact, I've never seen it used appropriately, even though such cases exist, so I err on the side of "your prior should be heavily anti-EAV - please take note of all the skulls down this path and think carefully before proceeding yourself".

Datomic is in the same group, and I'd consider Rich Hickey to be one of the best programmers there are.

However, Derek was no Rich Hickey. Such deviations from the norm should be left to the type of person that can create Clojure, and very far away from the people that upload PII to GitHub. If we could create a culture where we slapped people's hands on instinct when they reached for MongoDB, I think we'd be better off.

The PII thing is on an entirely different page, and indeed sweat inducing :D

That's calibrated too far the other way but there'd be less damage.

It absolutely does happen in triple stores, though, where data is commonly stored in subject-predicate-object form, and the subject's identifier is certainly meant to be unique to that subject, but not per triple.

> 1 Name Ludic

> 1 Age 29

> 1 Profession Tortured Soul

> The key is not unique. There is no primary key. So to hunt down all the properties of User 1, you have to do a query for all the records whose Key is 1.

In this example, the primary key would be ([User ID],[Key]). The primary key does not need to be a single field.

Roll the related data up into materialized views for read performance.

Database design has many tradeoffs, and there is no free lunch.

IMO the real tragedy is that Datomic predated the open-source-but-you-can't-host-it-or-otherwise-operate-it-for-profit licenses that have proliferated recently.

If I ever get the time, I'll write a compiler. And if I have time left over on sabbatical, I'll reimplement Datomic as a Postgres extension (goddamnit).

Met this kind of person three times so far, been asking myself the same question. I suspect they live too much in their own head.

Only a completely idiot would use copyrighted code from their previous job, and work for months without putting anything into version control.

And, above all, not have anything actually working, like forms not actually saving data, after months of work.

Nope, bozo.

It was obviously a complete trainwreck, but it worked enough to convince multiple executives at different companies to actually pay for this crap. Sure it went down every week and was leaking data left and right, but it looked legit.

I clearly don’t appreciate the architecture or the reliability of it, but the tenacity of the people able to build such a monstrosity day in and day out, somehow wrangling it into shape against all odds. That’s the sort of mental willpower I wish I had.

Of course to scram once the going got rough.

In the words of TFAs author:

AAAAAAAAAAAAAAAAA

Note: I don't know anything about basketball.

So once you write a test harness in order to make sure your refractors don't break things, you start to notice that the things you're testing are wrong. The code doesn't work correctly, it is littered with bugs.

Actually just finished a refactor like that. In a fairly small refactor I found multiple things that weren't working correctly. It was sending duplicated messages, it was incorrectly labeling messages with duplicate sequence numbers, and lots more.

I wrote equivalent code to replace it, spending roughly half the number of lines and getting rid of a complete shitload of unnecessary method parameters and other pointless stuff. Also fixed a hot path with an unnecessary O(n^2) complexity (where n could be pretty high) and a bunch of other dumb crap.

However, over the years I have occasionally and reluctantly used the 'Derek table', for importing domain data with a dynamic schema, aware that I will be paying dearly for it.

My question is: What alternatives are there..? Other kinds of databases, maybe those used for 'big data'?

I must clarify:

(1) I know I could instead create proper db tables on the fly - this way, I _can_ have varying columns depending on each new set of domain data I import. However, IF I do this, I will now have to dynamically build my SQL queries, to refer to these varying tables and column names. The BUILDING of those SQL queries is not to fear, but the query execution of them is, since each new variant is a not previously seen db execution plan, and some of those will hit weird performance problems. I am painfully aware of this, because I have worked(still do) 'lifeguard duty' on a production database, where I routinely had to yet again investigate how a user this time had created a dbms-choking query with exactly this technique.

(2) In the derek approach, the approach would usually be to violently retrieve 'all the query-relevant records' (e.g., the contents of 'project'/'document'), and then do the actual calculation in code, e.g. C#. This of course has the downsides of

(2.1) - we must haul huge (relatively speaking) amounts of raw data off the db, since we are not summarizing it to the calculation-end-result before-hand. But this is also the 'benefit': We know we won't bother the db further than this initial and rather simple haul/read. (I AM aware I could also query the derek monstrosity directly, but I have seen enough of that to know to avoid that, to not bring the dbms to the curb.)

(2.2) This is an extension of 2.1: Since we are working with the raw data, we must 'pay' both for moving the big chunk of data over the network, and also/often for having it in working memory on the actual external processing/calculation server (this may not be true, if the calculation can be done piece-wise working on a stream). And, of course, there is the entire cost of 'a single table cell is now a whole db row'.

Echoing poor Derek, the benefits of the described approach, is that it actually takes relatively simple approach and code to build the solution this way, at a tremendous cost to resources/efficiency. If I did 'the right thing', I would have to write considerably more and more complex code, to handle the dynamic DDL/schema processing, to dynamically work with the real DB schema.

Back in the 90's, I would by necessity have 'done the right thing', since the Derek approach was doomed performance-wise then. But now, in the 2020's, we have so much computing power, we can survive - for a time - by wasting extravagant amounts of resources.

To recap/PS: Whenever I have done this, it has been for a small subset of specific data; I have never done it in the insane "one single table", with dynamic tables too. My case has always been 'dynamic fields'.

Also, for context: The 'calculation' to be done, typically amounts to what could generously be referred to as a bit pivot-table operation on a heterogeneous set of data (which is why, expressed on proper SQL, the query would be rather verbose and unwieldy, accounting for all those heterogeneous and possibly-present fields on the different source tables/datasets)

https://ludic.mataroa.blog/blog/tech-management-has-bestowed...