QuackFuzed [ Ramblings of a Con..ColdFuzed Mind ]

Transfer: Many-to-One or One-to-One?

Posted on November 9, 2008 at 2:20 PM in ColdFusion, Transfer

This post is actually a response to a post by Ray Camden. I planned to add a comment to Ray's blog, but I felt it was too long to be considered merely a comment. Here's a snippet from Ray's post that I am specifically responding to:

Ok, so the point of this entry was to talk about relationships, so it's about time we got to it. Transfer defines three types of relationships: ManyToOne, OneToMany, and ManyToMany. We want to link an employee to a department, so which do we choose?

Here is where I run into one of my problems with Transfer. When I think of "Link an employee to a department", I naturally want to look for a OneToOne relationship. One employee belongs in one department, right?

However, that's not how Transfer looks at it. Every single time I use Transfer I have to double check this because it just seems... wrong to me, but Transfer looks at it as a ManyToOne. Ie, many employees are in one department. Again, this just does not click for my brain.

Many-to-One

The reason Transfer looks at Employee > Department as Many-to-One is because it **IS** an m2o relationship. It would be impossible to have this as a One-to-One (o2o) relationship, because it simply isn't one. Think about your database tables for a moment...

A) Employee: How many Department records can a single Employee record be assigned to? One? Many?

B) Department: How many Employee records can be assigned to a single Department record? One? Many?

The only answer to (A) is One, and the only answer to (B) is Many. Therefore, in terms of relationships, we know we are working with either One-to-Many (o2m) or Many-to-One (m2o). The only question is, which way do we want to model it?

To answer this question, we want to think about the model from the perspective of the view. Paul Marcotte has an excellent post about this on his blog.

Using the Employee > Department example, the Employee view is almost guaranteed to need the Department, whereas the Department view, more than likely, will only occasionally care about the Employee. Knowing this, we'll want our relationship to be readily available to the view on our Employee object. This means that we're going to add an m2o relationship to our Employee.

As an aside, I would choose to utilize a bit of TQL that would allow the Department view to get information about the Employee side of the relationship, but that's an entirely different post.

One-to-One

So then, what would be a good example of a true One-to-One (o2o) relationship?

Keeping with the Employee theme, let's assume that our application has a User table, which is used by our login system. This would be our super class. Employee is a subclass of User. We'll ask the exact same two questions about these two tables:

(A) User: How many Employee records can a single User record be assigned to? One? Many?

(B) Employee: How many User records can be assigned to a single Employee record? One? Many?

This time, the answer to both (A) and (B) is One, making this a true o2o relationship.

Summary

It is correct that Transfer currently does not support One-to-One relationships out of the box, but it is critical that one understands the difference between Many-to-One and One-to-One.

Employee > Department = Many-to-One

User > Employee (or Superclass > Subclass) = One-to-One

HTH

Comments

(Comment Moderation is enabled. Your comment will not appear until approved.)

On 11/9/08 at 4:19 PM, Justin Carter said:

Yeah, in this case it's definitely a Many-to-One relationship, many Employees belong to one Department. If you were to enforce referential integrity at the key level as a One-to-One relationship the database would complain as soon as you tried to assign a second Employee to a Department that already had an Employee assigned to it.

On 11/9/08 at 11:52 PM, Raymond Camden said:

My problem with your argument is that it is logical. ;) I'm not saying that ManyToOne is wrong - I'm just saying it isn't how my brain works. :) You said, "Think about your database tables for a moment...", but isn't the point of an ORM to stop thinking about the database and start thinking of the model?

Also, I get that there are many emps to one department. However, when I'm defining the emp object, I look at it as defining _A_ employee. To me, _A_ employee maps to _A_ department.

On 11/10/08 at 12:17 AM, Matt Quackenbush said:

@ Ray- Okay, so ignore the database. It's completely irrelevant. Let's look at it from the model's perspective. We are talking about a relationship between two entities: Employee, and Department.

A relationship is a two-way thing. So you *have* to look at it from both angles.

(A) Employee Object: How many Departments am I permitted to have? One? Many?

(B) Department Object: How many Employees am I permitted to have? One? Many?

The answer to (A) is still One, and the answer to (B) is still Many. No matter how you slice the pie, Employee > Department is a Many-to-One relationship.

On 11/10/08 at 12:22 AM, Raymond Camden said:

That makes sense. I'm not saying it doesn't. I'm just saying my brain is 'sticking' on it. ;) And it sticks on it every time I work with Transfer. This last comment though is merging nicely into the gray matter, so maybe next time it will be easier. :)

On 11/10/08 at 2:59 AM, John Gag said:

I believe that a one to one relationship is rare in a database design but they do occur. Also, a many to many usually needs a child table and should rarely exist also. My 2 cents

On 11/10/08 at 4:49 AM, duncan said:

John, I frequently have to use many-to-many child tables. Why shouldn't they exist? For instance, suppose you have the situation where one employee can actually belong to two departments? Or lets say you have a table for Products, and another for Colours. Each product comes in many colours; each colour can belong to multiple products. Without denormalising your tables and having a FK column that lists all the options, how do you deal with this without the child table?

On 11/10/08 at 12:02 PM, John Gag said:

@Duncan - Many to many relationships only exist in a denormalized form. In reality you should have a child table between the many to many creating two one to manys. This child table can also be referred to as a junction table or a detail table. I believe this should be done by 3rd normalized form.

On 11/10/08 at 1:40 PM, duncan said:

ah, I misunderstood you, I thought you were saying you shouldn't use the child tables to do the 1-M / M-1 join between the two parent tables.

On 11/10/08 at 1:46 PM, John Gag said:

@Duncan - Yeah, maybe I should have been a little more clear my first post :)

On 11/15/08 at 12:38 PM, Gerald Guido said:

Thanx Matt,
I am just starting to get my head around this and as you know the initial learning curve is often contains the biggest stumbling blocks. The is really helped me get it straight in my head.

[Add Comment]