This is a question that crops up a lot, in various forms, on the Fluent NHibernate and NHibernate Users mailing lists. My one-to-one mapping isn’t working, what’s wrong? aka Incorrectly using a one-to-one relationship when you actually need a many-to-one.
There’s a common misunderstanding where people try to use a one-to-one relationship where a many-to-one is appropriate. I believe this is because people tend to get tunnel vision when designing their entities, which leads them to make incorrect assumptions. They focus on one entity at a time, and when that has a single entity related to it, they jump to the conclusion it’s a one-to-one they need; after all, there’s their current entity (one) and the related entity (to-one). They’re actually forgetting that there can be multiple instances of their current entity.
There’s also a big distinction between what’s possible in the domain, and what’s possible by design in the database; for example, two businesses may never share an address in your application, but in the database there’s nothing stopping two rows in the Business table from referencing the same address row in the database. This is logic that can be applied on-top of a database design, but there’s nothing in the underlying pattern to disallow it.
Many-to-one
Lets have a look at what actually is a many-to-one. Here’s a small database schema and the related entity.
table Customer (
Id int primary key,
Name varchar(100),
AddressId int foreign key (Address.Id)
)
table Address (
Id int primary key,
Number int,
Street varchar(100)
)
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public Address Address { get; set; }
}
This is a standard many-to-one relationship, many Customers to one Address; the tables are linked by the AddressId key in the Customer table. You can see how people can misinterpret this as a one-to-one relationship when designing the Customer entity; the customer has one address, so it must be a one-to-one. People forget about this scenario:
| Id | Name | AddressId |
|---|---|---|
| 1 | Plumbers | 1 |
| 2 | Joiners | 2 |
| 3 | Roofers | 1 |
| Id | Number | Street |
|---|---|---|
| 1 | 23 | Baker St. |
| 2 | 47 | Jefferson Ave. |
That is, the first and third customer both reference the first address.
One-to-one
So what actually is a one-to-one relationship then? A one-to-one is a relationship between two tables that share a mutually exclusive primary key value.
table Customer (
Id int primary key,
Name varchar(100),
)
table Address (
Id int primary key,
Number int,
Street varchar(100)
)
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public Address Address { get; set; }
}
You can see in this design Customer has no direct reference to Address, the two tables share a primary key value; so there would be a record in Customer with a primary key of 1, and a record in Address that also has a primary key of 1. It’s fairly common to have the primary key on the second table (Address in our case) be manually inserted on creation of a record in the first, so it may only be the first table that has a true auto-incrementing primary key.
It’s also noticeable that both examples have exactly the same class design, this probably contributes to the confusion too, as it’s not immediately clear from the class what kind of relationship it is.
So just remember this: if you think you’re mapping a one-to-one, you probably aren’t! It’s pretty uncommon to find a one-to-one relationship in a properly designed schema, 90% of the time it’ll be a many-to-one you need.
Comments...
James,
I know as i have been learning NH over the past few weeks I have made this mistake a number of times.
Thanks for the clear explanation of the 2 mappings.
By Derik Whittaker — 1 Jan, 2009 @ 11:14 am
Nice post. You *might* be missing a foreign key constraint there.
table Customer (
Id int primary key,
Name varchar(100)
)
table Address (
Id int primary key,
Number int,
Street varchar(100),
foreign key customer_id (Id) references Customer(Id)
)
So, the address pk is also a fk to customer, enforcing that you can’t have an address without a valid customer id.
By Tobin Harris — 1 Jan, 2009 @ 3:43 pm
Tobin: You could do that, but I’ve never seen it done when intended to be a one-to-one; as a one-to-many yes, but not a one-to-one.
By James Gregory — 1 Jan, 2009 @ 5:05 pm
Yeah, I rarely see anyone make a single column both a primary *and* a foreign key.
I like the fact you get constraint checking for free: You can’t add an address if there’s no corresponding customer, and you can’t delete the customer without first deleting the address.
By Tobin Harris — 1 Jan, 2009 @ 5:50 pm
Thank you very much for that nice explanation.
By Florian Fanderl — 3 Mar, 2009 @ 11:43 am
Indeed, a foreign key to customer in the address table would create a one to many relationship (one customer can have many addresses).
The reason that one to one relationships are rare is due to the fact that they generally result in both pieces of information being stored in the same table (i.e. if a contact can only have one phone number then you just have a phone number column in the contact table).
James correctly demonstrated the one to one relationship having 2 tables that both share the same id.
By almasmith — 12 Dec, 2009 @ 10:53 pm
How is this mapped? HasMany mapping requires a collection, not a single object.
By Mike Cole — 4 Apr, 2010 @ 2:43 pm
This isn’t really anything to do with HasMany. It’s for many-to-one and one-to-one relationships, which are (in Fluent NHibernate) References and HasOne respectively.
By James Gregory — 4 Apr, 2010 @ 1:56 pm
On the FK side, when you have an fk + uc and the fk fields are constrained by the UC, and are non-nullable, pointing to a pk, this too is a 1:1 relationship. NHibernate ignores this as if it doesn’t exist but that’s not correct. This puts people off, as the m:1 they have to map with a ‘unique’ attribute is really silly, it’s not a m:1, it’s a 1:1.
For o/r mappers internally, the 1:1 relationship is a cumbersome thing: you have to keep track which side is the fk side, and you have to check whether the fk side is constrained by a UC or a PK constraint. But that doesn’t mean it only exists in the pk-pk form, as the fk/uc-pk 1:1 is a true 1:1 relationship as well.
By Frans Bouma — 6 Jun, 2010 @ 11:15 am
Frans: Always with the logic, eh? Thanks for the comments.
The main point I was trying to get across with this post is that for 90% of the time, people genuinely do want a m:1 relationship. I have no doubt that there are situations where 1:1 are useful, but I think beginners struggle to tell the difference (primarily because 1:1 is such a vapid and overloaded term in the db space).
As for the o/r side of it, yeah, no disagreement here.
By James Gregory — 6 Jun, 2010 @ 10:53 am
Post a comment...