I've search around for an answer to this and found one post but not a great solution.

Say your DB is something like:

TableOne
-----------
TableOneID (PK) (NOT NULL)


TableTwo
-----------
TableTwoID (PK) (NOT NULL)
TableOneID (FK) (NULL)


Now, this is obviously a classic many to one relationship (just that it allows NULLs), but in my case I know that having a TableOne related to a TableTwo is rare and this column is 90% NULL. So, I would like to change the DB to the following.

TableOne
-----------
TableOneID (PK) (NOT NULL)


TableTwo
-----------
TableTwoID (PK) (NOT NULL)


TableTwo_TableOne
-----------------------
TableTwoID (PK) (NOT NULL)
TableOneID (NOT NULL)


I know I can do this using a one-to-one mapping, but that results in code like TableTwo.TableTwoTableOne.TableOne when I'd rather not have to create the intermediary object and just do TableTwo.TableOne -- anyone know how to do this in NHibernate?

(For a little context this of both TableOne and TableTwo as objects that exist on their own and are both useful on their own, but we ran into a situation where it's useful to attach a single TableOne to a TableTwo.)

hi mate,

you can find everything in this blog

http://sdesmedt.wordpress.com/?s=NHibernate

good luck!

Thanks for the link, but I've been there before. That blog has some good NHibernate stuff, but I couldnt find anything on doing a one-to-zero-or-one relationship (1 to 0..1).

If I'm missing something, maybe you could point me to a specific entry or comment? Thanks!

Ok,

let me se If I got what you said.

if don't want to use classic one-to-one...you could consider that relationship as a collection mapping just with one record as well and retrieve just a UniqueResult.

that would solve the problem?

The reason classic one-to-one doesnt work is because the primary key is shared. In this case I have two distinct objects that are valid and can exist and be used separately.

Think of it this way.. say you have an Address table that is used for shipping, billing, and home addresses for Users. You also have a CreditCard table. In the normal case, since a billing address is requird for a credit card, you would just stick a BillingAddressID in the CreditCard table that was a FK to Address.

Now, to make this example parallel with my case, we have to make the billing address optional.. you can either have one billing address attached to the card or none. Since Address has its own PK field that is separate and distinct from the CreditCard's PK field, then we cant do a classic one-to-one, you need a junction/associative table, something like CreditCard_BillingAddress and this table would have two columns, the first would be CreditCardID (PK) and the second would be AddressID (not part of the PK). Now we have a 1 to 0..1 relationship.

I can easily get this to work with NHibernate if I create CreditCardBillingAddress class and map it to that table, but it is not ideal as the usage would be something like creditCard.CreditCardBillingAddress.BillingAddress and I see no reason to have that intermediary class. The usage should be like creditCard.BillingAddress

You may be wondering why I dont just add a BillingAddressID to the CreditCard table and just allow NULLs on it. The reason is that very few CreditCard records will actually have a BillingAddress, so this column would contain somewhere around 95% NULL.

It may be that I just have to accept that, which I'm prepared to do, but I'd much rather use the more normalized 1 to 0..1 to get rid of all those NULLs.

mate,

I got what you need..

of course you shouldn't leave a property that's going to have 95% NULL.

in my application I have a similar situation...

there are 3 entities

- Blog
- Article
- Poll

each one can be market with a tag. ( tag clouds implementation )..
so there is a entity named "Tag"

as the relationship is many-to-many...it's necessary 3 other tables to stored the data

- BlogVsTag
- ArticleVsTag
- PollVsTag

naturally there is table named "Tag"


I didn't create a three anothers object to implement that.

each Entity (Blog, Article, Poll) has a property that gives a Collection of Tags. Can be found just one record, hundreds or none.

In your case, could be used the same approach, Address table never would be empty...and the another table "CreditCard_BillingAddress" only would have records when necessary.

here the possible implementation.


--------------------
CreditCardTable
--------------------
ID | 10
CreditNumber | XYZ
...
...
so on
--------------------

--------------------
AddressTable
--------------------
ID | 20
CreditCardID | 10
...
...
so on
--------------------

or depending of business logic

--------------------
CreditCardTable
--------------------
ID | 10
CreditNumber | XYZ
...
...
so on
--------------------

--------------------
AddressTable
--------------------
ID | 20
...
...
so on
--------------------

( just 2 columns)
--------------------
AddressXCredicard
--------------------
AddressID | 20
CredicardID | 10
--------------------

that would solve the problem to be found null record without need.

I don't know if you will understand my english..I hope you get it.[/code]

Yes, I understand what you are saying and that is another solution I've considered. The one thing I dont like about that solution is that the AddressList collection will only ever have 1 item in it and so it is overkill in that regard. It also implies to consumers of the object model that there could be multiple addresses per card when that is not allowed. What happens if someone tries to do creditCard.AddressList.Add() when there is already one in there. This violates a business rule.

I may just be pushing the edge of what NHibernate is currently capable of doing. I may have to just make a sacrifice on weither the domain model design or the DB design.

hum..

I didn't realize that you would have just one item in that relationship.

Son I will need to work with a relationship so similar...

If you find a better (elegant) solution to solve that, please

I wonder if you could let me know

;)