7

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.

  • I think it's about setting expectations. This student has a birthplace (many to one, required, a property) each student only ever has one birthplace, not zero not three, so you can expect an object here.....This student also does courses, (many to many relationship, not even required, optional) so here you can expect an array of possibilities, even an empty one. – Pieter B Aug 19 '16 at 14:57
  • In that example I would say: The Student object contains a single object/field of type Birthplace, and a collection of objects of type Courses. And a Course object would have a collection of Student objects. – The 29th Saltshaker Aug 19 '16 at 15:07
  • I know I am late to this, but several of the students will probably have the same birthplace if it is a city. If the school is a primary school many of the students would even have the same hospital. It is a design decision that would need to be made -- "is reducing the birth location to a reference in another table the best use of resources?" – Jeff Zacher Feb 13 at 18:50
2

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 : Example of difference between Many-to-Many and two Many-to-One relationshps

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 :

enter image description here

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;
}
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.

  • I am so confused now because your last example is precisely what I am talking about: If I want to say "a Customer can have one billing address but many shipping addresses," then a Customer would contain a single billing address reference, but a collection of shipping address references. The cardinality in the relationship is encapsulated in the cardinality of the references: A single reference = a one relationship. A collection of references = a many relationship. – The 29th Saltshaker Aug 19 '16 at 14:55
  • @The29thSaltshaker correct, except in this case there are two separate relationships to Address. One is 1..1 and one is 1..n – user22815 Aug 19 '16 at 15:04
  • 1
    Yes, I would say: There is a one-to-one relationship between Customer and billing address, and a one-to-many relationship between Customer and shipping address. – The 29th Saltshaker Aug 19 '16 at 15:05
  • Right, so, what's the problem? In the question you indicate you don't quite understand why the cardinality must be specified (but in more words), yet here you say you understand why. – user22815 Aug 20 '16 at 3:31
  • I don't understand why the cardinality has to be explicitly mentioned (e.g. in terms of annotations) if the cardinalities are implied by the data types – The 29th Saltshaker Aug 20 '16 at 3:54
2

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.

  • What if there are no many-to-many, but there are multiple many-to-one/one-to-many/one-to-one (or a mix) between A and B? Then would a A_B table be needed? – The 29th Saltshaker Aug 20 '16 at 15:48
  • @The29thSaltshaker: A one-to-many relationship is typically mapped to a simple foreign key relationship in a relational DB, (same as a 1:1 relationship) , no extra link table needed. This does not change if you have multiple of those relationships, even if they are all between the same table (just the foreign keys need differents names). However, not to distinguish between 1:1 and 1:n would still lead to wasteful and overcomplicated client code, even if the relational model looks similar (almost, for a 1:1 relationship the ORM could generate a unique constraint to ensure better integrity). – Doc Brown Aug 20 '16 at 16:41
  • I think I am asking a slightly different question. As in, let's say we have two objects A and B. We can create, at the same time, several relationships between them -- one to one, one to many, many to one, but no many to many. In terms of the backend, would this still only be represented with two tables? A third table would only be introduced if there existed a many to many relationship? – The 29th Saltshaker Aug 20 '16 at 16:53
  • @The29thSaltshaker: I don't see a different question. My answer is still the same: no, you don't need a separate link table. Are you not aware how the typical Object-to-Relational mapping works? Make yourself through a tutorial like this one: agiledata.org/essays/mappingObjects.html – Doc Brown Aug 20 '16 at 17:00
  • I have read through this before -- I really find these tutorials frustrating because a lot of it is "common sense" and they don't get into the more complex details that you actually encounter. – The 29th Saltshaker Aug 20 '16 at 17:11
-2

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.

  • 1
    How is this relevant to the question that was asked? – Robert Harvey Aug 19 '16 at 16:59
  • 1
    This isn't related to my question. I already understand that an ORM maps relationships. I am asking why it is necessary to mark a particular relationship with regards to its multiplicity. – The 29th Saltshaker Aug 19 '16 at 17:07
  • An ORM maps relationships. Database entity relationship diagrams always include multiplicity. Knowing what entities reference each other is not the same as knowing their relationships. If you would rather just have something that tracks references, you can do that. However, that by definition is no longer an ORM in the same way that lemonade without lemons is no longer lemonade – David Cram Aug 19 '16 at 18:08
  • But isn't multiplicity implied by the nature of the container? If object A has a collection of object B's then there is a one to many relationship between A and B – The 29th Saltshaker Aug 19 '16 at 18:14
  • There is no such thing as objects or collections. The nature of the container is that it is a database entity. Objects and collections in the ORM are just smoke and mirrors on top of the database entity. At the end of the day you are still using a database which uses entities and relationships, not objects and collections. – David Cram Aug 19 '16 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.