ORM or how to forget about database design

From the author

A year has passed since I changed the profession of network administrator to the profession of a programmer. This year was a lot of unusual and strange. I managed to work closely with sqlalchemy, take a look at ponyorm, play with hibernate and nhibernate, refresh models from django ... and you know what? All the code that I saw was associated with the consequences of the activity of a monkey with a reserve of grenades ... it turned out that letting programmers into the database was not the best idea. There is a lot of pain and hatred under the cut, but suddenly it will come in handy for someone.

Before starting, I want to introduce a couple of assumptions:

1. All text is IMHO
2. All names and cases are synthetic. Any coincidence with real projects and people is an accident.
3. The author has big problems with the Great and the Mighty (we fight through personal messages)

What is an ORM?

Before you teach someone the mind, it is worth understanding what the term ORM is. According to the analog of TSB , the abbreviation ORM stands for bourgeois “Object-relational mapping”, which, translated into Pushkin’s language, means “Object-relational mapping” and means “programming technology that connects databases with concepts of object-oriented programming languages” ... i.e. . ORM - a layer between the database and the code written by the programmer, which allows the objects created in the program to be added / received to / from the database.
Everything is simple! Create an object and put it in the database. Need the same object? Take from the database! Ingenious! BUT! Programmers forget about the first letter of the abbreviation and they all stick to the same plate! Starting from the properties of objects, which is logical, and ending with a foreign key, that has nothing to do with the object! And, worst of all, many tons of howto and example propagate this approach ... it seems to me that the root cause lies in the constant balancing between “I am a programmer” and “I am an architect of the database”, as ORM multiply and multiply - the voice of a programmer puts pressure on the architect. Everything, there is no further pain, only imho.

“Who are you, Mr. Brooks?” Or “What is an object?”

How to define "what is an object" in ORM? How many people will be able to convey the whole point the first time? Let me try.
Suppose we all live in Belarus / Ukraine / Russia, just like me. Under the law, gay marriage and polygamy are prohibited in the country. At the same time, there is the concept of “military duty” and other “specific” properties for each gender. What follows from this?

1. For each gender, it is advisable (but not essential) to have a sign
2. Somewhere you need to store information about the husband’s wife and wife

“So this is OneToOne! Classic! What's so complicated? We define fkey at the line for male or female and ran on ”- yeah, I can read minds. BUT! A year later, our software became interesting to Arab sheikhs and they are perplexed, how so, by no more than 1 wife?!?! Yes and to whom to add fkey? The male gender? Female? And if everything is in one table - who will write and accompany the logic checking that a lady of an advanced age will not have a spouse?
Unclear? OK. Let's pseudo-code:
man_1 = new Man()
man_1.name = "Иван"
woman_1 = new Woman()
woman_1.name = "Таня"
???
And then what?
man_1.wife = woman_1or woman_1.husband = man_1
And how to save IT in a DB? But no way! In the example, the obviously wrong approach to the data structure!
The name property of the woman_1 and man_1 objects is a property of the ORM object, but the wife property of the man_1 object and the husband of the woman_1 object are already relations of ORM objects! THESE ARE DIFFERENT PROPERTIES!
Still not clear? OK. Let’s approach the issue even more humanitarian language.
There is Ivan and there is Tatyana. If they are rejected then? Right! They will not change! And if you tear off your hands (or other important organ) to Ivan and shave off your hair Tatyana - they will change! So, marriage is the attitude of objects, and hands and hair are properties of objects. Everything, I do not know how else to explain.

The plight of OOP

At the very least, we have come to understand the object. It became quite clear who is who. But how to save IT in a DB? Some comrades with smart faces and long beards advocate the statement "everything is an object." Let's try to stick with the same thing since we are considering ORM. Suppose for objects Man and Woman we use different tables. Where will we store the “object” of their relationship? Right! IN SEPARATE TABLE! Let's call her woman_and_man. There will be only 2 columns representing fkey in the plate so far: man_id and woman_id.
“Wait, my dear, this is already ManyToMany of some kind!” - yeah, I'm still reading your thoughts! In principle, I agree with you, with one maaalenkoy amendment. If you define the correct constraint for the woman_and_man table, it turns into OneToOne with a flick of the wrist. Do not believe? We try!
For OneToOne, we need to observe the following rules:

1. One woman cannot have more than one male husband
2. One man cannot have more than one female wife

We introduce the corresponding constraint:

1. the value of man_id within the table woman_and_man must be unique and not null
2. woman_id value within woman_and_man table must be unique and not null

All! We have OneToOne!
We send the software for export to the UAE - change the constraint for man_id and get ManyToOne / OneToMany! Please note that the table structure will not change, only constraint will change!
We send the software for export to a country with mutual polygamy / polygamy (and there are such ?!) - change the constraint for woman_id and get ManyToMany! Please note that the table structure will not change, only constraint will change!
Check your ORM - you will find an example of using OneToOne / ManyToOne / OneToMany through ext. table.

Dedicated to critics

After expressing my thoughts to the leader, I received the expected response: “Why complicate this? KISS! ”
I had to“ gain experience ”:

There were cases with cyclic connections between objects containing fkey properties and the task of“ backup / serialization ”of this disgrace in xml / json. No, backups are done, then it’s damn difficult to restore it later ... you need to strictly monitor what properties are created during restoration / deserialization, and then repeatedly go through the objects and restore the connection between them. Adhering to the rules above - you must first restore the objects, and only then the connections between them. Because this information is stored in different tables / entities - the logic was linear and simple.

For each “take monga and don't worry” or “document-oriented databases rule” attack, I always came to the same result that no one could cover:
I can create a scheme in a relational database that will save an arbitrary data structure (arbitrary documents ), but can you guarantee data integrity at the level of relational databases in a document-oriented database? I could not reach that level.
Doesn’t anyone bother with multiple pieces of duplicate documents with an arbitrary level of nesting? No, I know that their storage is optimized and just do it, what's the difference to you? But still.

PS This is not the whole "stream of consciousness" associated with ORM. There are a couple of paragraphs on null, a couple on query optimization and deferred loading of objects, a paragraph on relationship properties (yeah, and it happens), but is this relevant? I ask critics to comment in comments. I will include especially acute and interesting questions in the sequel.