how do I map this?

Question

I am sure I am stupid (which some arrogant clerk at my bank just explained to me) but I don´t get how to map this:

lets say I have a table tbl_products with a ID column and a tbl_language with a ID column and my problem: a tbl_product_texts without any id of its own but a product_id and a language_id (and a lot of texts in a couple of languages)

do I have to add a ID column to my table (which is ugly) or can nhibernate keep track of that in private somehow and is this kind of datamodel conceptually wrong or something?

Answer 1

If your trying what I think your trying then You set up a many-to-many mapping between tbl_Products and tbl_language. In the mapping just let nHibernate know which table serves as the link, nHibernate is clever enough to work it out.

https://www.hibernate.org/hib_docs/nhibernate/html/collections.html

So in the Products mapping (presuming you want a collection of Languages):

<bag name="nameOfLanguageCollection" table="tbl_product_texts">
  <key column="productID" /> <-- Name of ProductID col in tbl_product_texts
  <many-to-many class="LanguageClass" column="language_id" />  <-- name of languageid in   tbl_product_texts 
</bag>

I think that's right. Can't check now.

Answer 2

Product has a one-to-many relationship with ProductText, so you would model Text as a collection on your Products class. You tbl_products_text should have a primary key. You don't state what database you're using but my approach would be to use a surrogate primary key (identifier) and a unique constraint on product_id, language_id. You do not need to take that step to get NHibernate working if you're not managing the texts in your application.

A first pass at your Product class would look like:

public class Product
{
    public IList<ProductText> ProductTexts { get; set; }
    public string GetTextForLanguage(int languageId) { ... }
}