Why are dependencies typically reversed when representing complex objects as relational tables?

Question

I'm going through some sort of phase where I over analyze and second guess every single decision I make when attempting to write software that has preventing me from getting anything done.

I recently came across a need to store a"complex" C# object in a SQLite database. To boil down the object into a minimal example lets just say that this object, of type Node, has a list of children of type Leaf. While defining the classes most would agree it is good practice to keep any references to Node out of Leaf, this allows Leaf to be independent, reusable, and have a "single purpose".

public class Node
{
    public string Name { get; set; }
    public List<Leaf> Leaves { get; set;}
}

public class Leaf
{
    public string Name { get; set; }
    public int SomeValue { get; set; }
}

Now, when writing up the SQL table definitions, pre-meltdown plast1k would do something like this (and I assume the rest of the world would too):

Nodes
-ID (int, identity)
-Name (varchar)

Leaves
-ID (int, identity)
-Name (varchar)
-SomeValue (int)
-NodeID (int, foreign key)
-Index (int)

But we've now created a dependency between Leaf and Nodes. If you look carefully we also created a dependency between Leaf and List<>. The more I think about it (stop me here if that is in fact the only problem) the more I don't like this.

I have limited practical database design experience. My instinct was to start factoring out the relationship, which I later learned is called a bridge/linking table, and seems to be standard practice when the relationship is many to many.

Leaves
-ID (int, identity)
-Name (varchar)
-SomeValue (int)

LeavesMapping
-LeafID
-NodeID
-Index

This seemed fine at first, but started getting difficult to manage with real my real life data. I've also decided to optimized for re-ordering of the list items and am modeling it in the database as a linked list (using NextLeaf rather than an index, for example).

Is this really the right way to do this? Are there any alternatives? What constructs are missing from relational data models that force us to do this? Are there any databases that can model this in the same way that the code does?

Answer 1

Stop treating your relational database like an object store.

Basically, it sounds like you have objects that you are serializing into a SQL database. A SQL database makes an awkard place to store objects because of the Object-relation impedance mismatch. OO and relational view entities differently, and you'll find many cases that awkwardly map between them in addition to the ones you've already observed. As a result, your current usages just makes the relational paradigm look like a bad object store.

One possibility is to give up trying to use a SQL database. Instead use a document store database that naturally stores objects. Take for example a NoSQL database that stores JSON objects. You'll find it very natural to serialize your objects to JSON and store those. Depending on your choice of language, there are a number of projects that can fulfill this role.

Another possibility is to use SQL, and just deal with the impedence. Don't try to force your SQL tables to map 1:1 to your objects, that will just be painful. You should accept that your Leaf table knows about the Node table, and your code responsible for pulling the queries just has to deal with that.

Or, you can go whole hog and embrace the relational paradigm. Don't start with objects and serialize them into a SQL database. Start with the SQL tables and view any objects as derived from them. Instead of thinking about a Node as containing a list of Leafs, you should think of Node entities and Leaf entities having a one-to-many relationship which you model accordingly.

The relational paradigm is really powerful when you have a complex set of arrangment between different entities. But when you are just serialising your objects, it doesn't give you much. It's not really so much that the relational paradigm is missing features, but that it requires you to think differently about your problem, and put the relationship between entities first and foremost, and not to think of all relationships as composition.

Answer 2

You should not apply OOP design principles to how you represent data in a relational (SQL) database. OOP design principles are concerned about behavior and interactions between objects, about interfaces and encapsulation. None of this matters in database which is only concerned about data and consistency.

From the perspective of the database, there is a one-to-many relationship between nodes and leaves which means your first design is the correct. But how this relationships is represented in the application on the object level (what kind of collection, which directions of navigation is possible and so on) is not of any concern to the database.

It is natural to think of table rows in a database as corresponding to objects, but the analogy breaks down if you push it to far. A row does not represent an object, but is a statement about a relation between data. For example the table Leaves has a foreign key to Nodes - this does not state that a Leaf "has a" Node in an OO sense or that a Leaf "knows about" this Node or vice versa. It only states that there is one Node for each Leaf, which can be represented in various ways in an OO model.

Coming from an application-development background, it might seem like relational databases are just a cumbersome way to "persist" objects. But from the perspective of a relational database the data itself is the important thing, and applications are just a custom "user-interface" which allows you to manipulate the data. Relational databases are designed to let multiple independent applications interact with the data, so the schema design should not be dependent on implementation details on any particular application. Other applications might not even be OO programs, but might be say a functional program or maybe a user writing ad-hoc queries.

The database schema should guarantee that no application can make the data inconsistent. Preferably by making it impossible to represent invalid data in the schema. Your second design with a LeavesMapping table is dangerous since it would allow someone to let a leaf be associated with two nodes. This is simply not possible in the first design, which therefore better represent the inherent constraints of the data model.

---

As for Index versus NextLeaf, both approaches has the problem they treat order as an inherent property without any semantics. You should first clarify what the semantics of the ordering is. A directory tree for example does not have any inherent ordering, but might be displayed using a ordering selected in the UI, based on inherent properties like name, creation date and so on. So you would store these properties but no specific ordering. If it is a family tree, siblings are conventionally ordered by data of birth, so again you don't need a separate field for ordering.