Can anyone explain this one-to-one, one-to-many, many-to-one, many-to-many concept with respect to ORMs?

Question

A lot of ORM sites and SQL tutorials mention these relationships as if they are obvious or to be taken for granted, but I don't fully understand why the distinctions need to be made.

Consider two tables A and B, both with id fields, and linkage(s) between these two tables.

My understanding is that:

1. One-to-one relationship: If you have a row in A, you have at most one corresponding row in B, and vice-versa. I think in this sort of scenario you could probably just combine the tables, possibly, but that's beyond the scope.

2. One-to-many relationship: If you have a row in A, you can have any number of corresponding rows in B. But if you have a row in B, there is at most only one corresponding row in A.

3. Many-to-one relationship: Already described in 2.

4. Many-to-many relationship: If you have a row in A, there can be any number of corresponding rows in B. If you have a row in B, there can be any number of corresponding rows in A.

So if this is the case then why, in an ORM, do we have to make the distinction between these cases? Why does it not suffice to say, "Here is object A. It holds either a reference or a list of references to object B." Why do I have to explicitly mention "This reference to object B? That's a one-to-one relationship." Or "this is a many-to-many relationship," etc.

Answer 1

Because connection multiplicity is extremely important thing. And with important things it is better to be explicit than having magic figure things out.

What if for example A has list of references to B and B has list of references to A. Is this many-to-many relationship or two many-to-one relationships?

Just because A has reference to B and B has reference to A doesn't make it same connection. And if you cannot be sure, it is better not do do anything and leave everything to user of the library. ORM cannot get enough information from the object model itself. That is why additional metadata, either in form of XML or annotations is necessary to properly establish connections between entities and their multiplicity.

Something like this :

Another way how to look at it is like this : with many-to-many relationship, if item of A references item B, the system also guarantees that B will reference same item A and vice versa. With two many-to-one relationships, this is not the case. Item of A can reference item of B, but same item B doesn't have to (but can) reference same item A back.

I have a question for you, how would classes for two cases in this diagram look like :

Ok, last try trying to teach basic modelling. What relationships are in these classes :

class A {
    List<B> RelationX;
    B RelationY;
}

class B {
    A RelationX;
    LIST<A> RelationY;
}

Answer 2

ORM is a object-oriented representation of data in the database. Let us assume we have two classes A and B as in your example. How do we store references that model those foreign-key relationships as you have them in the database?

class A {
  B b;
}

Or:

class A {
  Collection<B> b;
}

That is the crux of the issue here: is there a one-to-one or one-to-many relationship? That changes what data structure we use in the generated code: a raw reference or pointer, or a collection.

For the popular languages such as Java and C# which use ORM and static type checking heavily in popular usage, this is a big deal. We need to know ahead of time not just what type of object is linked but how many. The reason is if there can be more than one, we need a level of indirection whereby we store the object references in a collection. The compiler enforces proper access using the correct type (e.g. B or Collection<B>).

---

You seem a little confused about the importance of this. It is not enough to know "A links to B" but also what the cardinality is.

Imagine a typical web ordering system such as Amazon or any one of a hundred other systems where you enter your personal information and buy stuff.

You typically enter your name and address among other details. That statement right there is huge, though: how many addresses do you have? Normally, only two: shipping and billing, and they may be the same.

Now imagine a system where cardinality is hand-waved away. Maybe I find a way to enter two billing addresses. Which does the system use? At the code level, is it accessing a collection of addresses and picking a random one? Or is there a method buried in there named Customer.getBillingAddress() that returns zero or one? What is easier to code to?

No, enforcing cardinality both at the database PK/FK level and in code by using statically-correct methods makes the system more robust and easier to understand when looking at the code. This would be the correct code model for my customer addresses:

class Customer {
  Address billingAddress;
  Collection<Address> shippingAddresses;
}

Now the code that manages the customer knows there is only one billing address, but I can easily enter multiple shipping addresses for myself, family, friends, etc. The code knows there can be multiple and perhaps iterate over them during checkout so I can select where I want my items to ship. But when charging my credit card, it uses the one and only billing address.

Answer 3

After thinking twice about your question, I am tempted to give a slightly different answer than the others here.

If you take an object model without any specified cardinalities for a relation between two classes A and B, an ORM could always treat it like a potential many-to-many relationship. So technically, it could generate or use tables A and B on your DB, generate an additional link table A_B, and generate all the related code. Since any many-to-many relationship on the DB could also be used to store just one-to-one or one-to-many relations, technically this would work.

However, this approach would have two big disadvantages:

1. It would be extremely wasteful. Note the additional link table would be not required if the ORM would know beforehand that a relation is just 1:1 or one-to-many. For example, having always to deal with superfluous records in the link table, as well as with SQL statements which are twice as complex as they need to be is a big waste of storage and CPU time. Same holds for your generated client code - getting a collection in a class just for storing one single value does make the code using this collection more complicated, it needs more memory and is potentially slower.

2. The database and the generated code cannot ensure data integrity for you, so if from a business point of view a relationship must not be many-to-many, but 1:n or 1:1, you have to implement this by yourself manually in the surrounding code. For example, when there is a collection used for a 1:1 business case, your program has to make sure it will only fill it with one value at maximum. If, however, your class would only contain a reference to a single object instead of a collection, it cannot be used in a wrong manner, so there is no additional code needed.

So in short, technically it would be possible, but it would be extremly wasteful and foil the goal of ORM's to make database programming easier.

Answer 4

ORM is actually an abbreviation for Object Relational Mapping. If you expect that you don't need a "relational" mapping then you really aren't talking about an ORM any more. I suppose you could create something that is just an Object Mapper but that would be something else entirely.