Creeping muck, or about problems with a single database of a single application

Today I want to voice one problem that a developer encounters as soon as work with a database is in his field of vision. The saddest thing is that I do not know how to solve it correctly, and what to do. Rather, I know what, but it does not help me and will not help. I think that you too.
Below there will be a long introductory, according to the results of which, I have no doubt, you can tell a lot about interesting things about me that will be enough for several 7-B forms and referrals for life-long compulsory treatment, but you can finish it.

This is an Enterprise, Baby

At the entrance we have software, a product with a complex business logic and a well-developed data scheme: there is a lot of data (tens of gigabytes), there is a certain order in the relations between them. A certain number of customers are completely perversely using business logic and data logic, and in fact expose various requirements to it.

“Different requirements” also means that all of them (customers) need different data. In their right mind, no one will use different databases for different clients, because this is brain damage , so all the data somehow lies in the same database. In addition to differing data, a different UI is also needed - determined by the client, by roles, a dozen other parameters of logic and the moon phase.

The first such offspring is fucking fields in the database by themselves. Have you ever seen a typical “data entry window for starting a business process”? Yes, yes it is right, be it amiss. 10 bookmarks, 100 fields on each. Each of them somehow (ahtung, magic!) Gets into the database, each of them was added during the long years of the program's operation - some by themselves, some - for analytics. They are added and added, until no one remembers why it was needed: a new field was needed - we added it everywhere, added it to the report, changed the analytics. It is good if the analytics counts as one sum / count / group_by.
')
B first of all, it turns out that the job of adding a new field to the table consists mainly of
- “add to database, add to table, add to objects, add to UI”.
- Attack another porridge in analytics / app workflow.

Taking into account the fact that, for example, sql is not subject to compilation, you need to be very careful to update the relevant in all places where necessary. In practice, “be careful” means that after the first compilation and deployment with a probability of 99%, the system will not work. Most likely, after the second ones too, there will be drops, errors, etc. It is possible to control it to some extent with the help of autotests; but it is stifling morally.

And finally, the second offense consists in this: you need to add two fields - 2 times more work. 5 fields - ... well, you understand.

Gradually, this crap spreads throughout the application; after a year or two, the application becomes subject to only one task - to figure out which fields and where to load / save / update. Something like this: govnokod.ru/1071

How could this happen to me? How to live now?

Making it inheritance (Active Record and not only) - will not work, more often than not it is simply impossible to build a hierarchy without getting crazy about finding the right relationships. If we score on the correctness of the object model, we will get crutches, expressed in the fact that in each place it is necessary to check whether we are working with the type of object - and work differently. We come to the option that after “add to objects” we will also have to deal with the hierarchy. In addition, my personal subconscious mind against inheritance in this place.

If you try to spread the model nicely, for example, in the Entity Attribute Value, then you get a complete ass with queries:
- writing them manually is even worse than simply understanding what is happening.
- performance crashes into the pipe: the DBMS server is enthusiastically busy with join, and the rest is ugly idle.

Another tested option is to forget about objects, and use metadata. This means that inside the target database another database is created with metadata, in which information about the structure and composition of tables and other facilities is duplicated. For them, a special editor is started, a layer of mega-abstraction is formed (on dynamic storage and view). With each server response containing data, metadata is also sent to the client from this data.

In addition to redundancy in data and schema, here we are waiting for a hell of updating the database, as well as the fact that the metadata itself also needs to work in a relational manner, which is extremely harmful for the developer's sensitive psyche: to understand recursion, you need to understand recursion. Attempts to squeeze some more or less complex UI behavior there are doomed to a heroic failure and a combinatorial explosion of the amount of metadata that need to be understood / remembered (Do you know how we made a notification in the system about changing fields? fields and compared with cached values, setting flags. As soon as an exception was thrown - and the previous developer even considered NullReference as a normal business case - the flow flew into the crust, and the alerts stopped coming. Do not rush to throw I galoshes, I removed it as soon as he heard. But the sediment remained, yes). And the “update this - update those” cycles have not gone anywhere. Fail.
And there is also the desynchronization of metadata and logic code. And there is also a DBA at the customer, for whom the update becomes oh-oh-oh with sausage playing Russian roulette.

And one more tested version (advanced) - self-written ORM + own object system. The hell with the fact that it is poorly adapted to work with relational data, we are still awaited by an ambush with updating the database schema. Yes, and the code becomes nasty - you need to sit and implement objects on top of objects again. From the writing of this very little joy is added. Well, if it is - just a directory. And if this is a lookup table or a segment of an ontological classifier? And the most interesting thing is how to deal with reports?
In search of truth, we have reached the point that when the application server starts by describing metadata, the code generator issues families of objects, through Aggregation Root which work with them. And when you connect, the library is passed to the connection, which contains the generated tools. Tin as it is, in short.
I generally keep quiet about the business logic of validation, process, etc., there is something terrible with scripts in “CustomSharp” (this is the language for confusing the description of business logic).

Misadventure

I think that it is no longer a secret to you that in practice I tried and accompanied all the described options, and I didn’t like any of them; however, with the last one, the zaparok was a little less, but still swallowed and continue to scoop the crap with unclean buckets.

So what to do? There is a feeling that I am sitting in a boat that is full of holes and that floats through the swamp, and in my hands are two tin walking circles that I can row to the shore, and I can scoop up all the water. Both should be done very quickly, and deal with priorities.

PS If we wrote in some magic languages, then each use case / script / process could be pulled out into a separate module that could monitor the data, load on demand, contain editors and attributes for mapping, and be combined when loading with the rest of these modules, both in the code and through the data, communicate with the database and transparently make structure updates. In this magical language, you can safely change the configuration of the system and sell it to a new customer - the core is the same, filling data / logic is different.

Does anyone know about such a magic language or system? SAP / MDM does not offer, this is a brand of epic pure power brand for the sake of a brand, almost unwearable. And do not pull me 1C or SAP in the foreseeable future :)

How, how are you fighting this damn trash with your hands?