Mapping one to many collection with Foreign Key to Foreign key?

Your relationship is an m:n relationship without an intermediate entity. It's a classic mistake to define m:n relationships this way, because it cuts out a pk side, which leads to the situation where there are two tables with the same attribute/field and they happen to semantically represent the same thing. However, because they're non-pk values in both sides, there's redundancy and also inaccuracy possible.

You can join the two tables together over the two fields but semantically it means nothing: for an entity model to relate entity X to Y, the FK side gets the PK side of the relationship as FK fields, and for m:n relationships, you need two m:1 relationships originating from the intermediate entity. That's it there's no exception.

Up vote 0 down vote favorite share g+ share fb share tw.

I'm mapping a legacy database with nhibernate and having some problems with mapping a realation. The two classes look like this public class Questionnaire { public int Id {get; set;} public string FormCode {get; set;} public IList Questions {get; set;} } public class Question { public int Id{get; set;} public Questionnaire Questionnaire {get;set;} public string QuestionText{get;set;} } to tables that are like this Questionnaire Table Id int FormCode varchar(100) Question Table Id int FormCode varchar(100) QuestionText varchar(max) The relationship between the two tables being the formcode column. My current mapping is like this When I run the mapping I get a Identifier type mismatch assuming it is trying to put the formcode into the Id of the question.

Unfortunalty I am unable to change the structure of the table and I am at a lost at how to map this and any help would be greatly appriciated. Colin G nhibernate nhibernate-mapping legacy-database link|improve this question asked Feb 8 '09 at 3:23Colin G530613 100% accept rate.

Your relationship is an m:n relationship without an intermediate entity. It's a classic mistake to define m:n relationships this way, because it cuts out a pk side, which leads to the situation where there are two tables with the same attribute/field and they happen to semantically represent the same thing. However, because they're non-pk values in both sides, there's redundancy and also inaccuracy possible.

You can join the two tables together over the two fields but semantically it means nothing: for an entity model to relate entity X to Y, the FK side gets the PK side of the relationship as FK fields, and for m:n relationships, you need two m:1 relationships originating from the intermediate entity. That's it, there's no exception. So although you want to map this the way you propose, it can't be done as the o/r mapper can't guarantee correctness because the suggested relationship is simply not correct.

THanks for the reply. I was afraid this might be the case. Need to go back to the client and see if I can change the structure of the database a bit.

Again thank you for the reply – Colin G Feb 8 '09 at 13:36 1 -1 How can it be an M:N when a question can only be in one questionaire? – cletus Feb 8 '09 at 13:58 @cletus: the two tables can be joined over FormCode as that's the attribute that's relates the two tables, which means that the resulting set could lead to a set which is m:n in nature, as it's not said that for every FormCode in Questionaire, there's 1 FormCode in Question. – Frans Bouma Feb 8 '09 at 14:08.

Well this is the "problem" with ORMs. I say "problem" because Hibernate is technically correct: foreign keys should be primary keys. But, as you're witnessing, that's not always the case.

Is ID actually used for anything? If not, the path of least resistance is to make FormCode the primary key. Is that an option?

If not, I'm really not sure what to do other than query the questions rather than treating them as child entities.

Thank you for the reply. I will go back to the client and see if I can get them to change their schema. Again thanks for the reply – Colin G Feb 8 '09 at 13:37 Bear in mind you don't necessarily have to change your schema.

It might be enough to tell Hibernate that the PK is FormCode (even if it isn't). – cletus Feb 8 '09 at 13:57.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.