ORM or how to forget about database design

From the author

A year has passed since I changed the profession of a 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 around 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 monkey's activity with a supply of grenades ... it turned out that letting programmers to the database is not the best idea. Under the cut a lot of pain and hatred, but suddenly someone will come in handy.

Before I begin, I want to enter a couple of assumptions:

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

What is ORM?

Before you teach someone the mind, you should understand what the term ORM is. According to the analogue of TSB , the abbreviation ORM hides the bourgeois Object-relational mapping, which in Pushkin’s language means “Object-relational mapping” and means “programming technology that links databases with concepts of object-oriented programming languages” ... i.e. . ORM - a layer between the database and the code that the programmer writes, which allows the objects created in the program to be added / received to / from the database.
It's simple! Create an object and put in the database. Need the same object? Take from the database! Brilliant! BUT! Programmers forget about the first little letter of abraviatury and write everything into the same table! Starting from the properties of objects, which is logical, and ending with the foreign key, which has nothing to do with the object! And, worst of all, many tons of howto and example are promoting 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", but since ORM multiply and multiply - the voice of the programmer weighs on the architect. All, 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 meaning from the first time? Let me try.
Suppose we all live in the territory of Belarus / Ukraine / Russia, just like me. According to the law, same-sex 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 sex. What follows from this?

1. For each sex it is advisable (but not essential) to have their own nameplate.
2. Somewhere you need to store information about the husband and wife accessories

“So this is OneToOne! Classic! What's so complicated? We define fkey at the line for males or females and run further ”- aha, I can read minds. BUT! A year later, our software became interesting to the Arab sheikhs and they are perplexed, so, no more than 1 wife?!?! And who should add fkey? Male? Female? And if everything is in the same table - who will write and will accompany the logic checking that the middle-aged lady will not have a spouse?
Unclear? OK. Let's pseudocode:
man_1 = new Man()
man_1.name = ""
woman_1 = new Woman()
woman_1.name = ""
???
What's next?
man_1.wife = woman_1 or woman_1.husband = man_1
And how to keep this in the database? 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, while the wife property of the man_1 object and the husband of the woman_1 object are already relations of ORM objects! THIS IS DIFFERENT PROPERTIES!
Still not clear? OK. Let's get to the question in a more humanitarian language.
There is Ivan and there is Tatiana. If they are banned then? Right! They will not change! And if you tear off Ivan’s hands (or another important organ) and shave Tatiana’s hair, they will change! So, marriage is a relationship of obets, and hands and hair are properties of objects. Everything, I do not know how else to explain.
')

Heavy OOP Legacy

At the very least, we have come to understand the object. It became quite clear who is who. But how to save this in the database? Some comrades with clever faces and long beards advocate the statement "everything is an object." Let's try to stick to the same thing, since we are considering ORM. Suppose we use different tables for the objects Man and Woman. Where will we keep the “object” of their relationship? Right! IN A SEPARATE TABLE! Let's call her woman_and_man . In the table so far there will be only 2 columns representing fkey: man_id and woman_id.
“Wait, my dear, this is already ManyToMany of some kind!” - aha, I still read your thoughts! In principle, I agree with you, with one minor 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 the OneToOne case, we need to follow these rules:

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

We introduce the appropriate constraint:

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

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

Dedicated to critics

After expressing my thoughts to the manager, I received the expected reaction: “Why is it so difficult? KISS! ”
I had to "gain experience":

There were cases with cyclic connections between objects containing among the fkey properties and the backup / serialization task of this outrage in xml / json. No, backups are made, so later to restore this disgrace is damn difficult ... you need to strictly monitor what properties are created during restoration / deserialization, and then re-run through the objects and restore the connections between them. Adhering to the rules above - you must first restore the objects, and only then the connection between them. Since This information is stored in different tables / entities - the logic was linear and simple.

For each attack, “take Mongu and do not worry” or “document-oriented database drives”, I always came to the same result that no one else could cover:
I can create a schema in a relational database that will retain an arbitrary data structure (arbitrary documents), but can you ensure data integrity at a relational database level in a document-oriented database? I could not reach this level.
Does 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 you, what's the difference? But still.

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