How to best design the entity data classes for the following SQL Schema?

Question

I have the following database schema:

http://lh4.ggpht.com/_SDci0Pf3tzU/SdM3XnAmmxI/AAAAAAAAEps/Ie3xW3ZVNfQ/styleerror.png

The issue is how to create the entity data class in Nhibernate?

Is this better:

public class Store
{
    public virtual int Id { get; private set; }
    public virtual string Name { get; set; }
    public virtual IList<Product> Products { get; set; }
    public virtual IList<Employee> Staff { get; set; }

}

public class Employee
{
    public virtual int Id { get; private set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual Store Store { get; set; }
} 

public class Product
{
    public virtual int Id { get; private set; }
    public virtual string Name { get; set; }
    public virtual double Price { get; set; }
    public virtual IList<Store> StoresStockedIn { get; private set; }
}

Or is this better? public class Store { public virtual int Id { get; private set; } public virtual string Name { get; set; }

}

public class Employee
{
    public virtual int Id { get; private set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual Store Store { get; set; }
} 

public class Product
{
    public virtual int Id { get; private set; }
    public virtual string Name { get; set; }
    public virtual double Price { get; set; }

}

public class StoreProduct
{
   public virtual List<Product> Products{get;set;}
   public virtual List<Store> Stores{get;set;};
}

I think the first one is easier to understand than the second, isn't it?

Frederik Gheysels Frederik Gheysels · Accepted Answer · 2009-04-01T12:21:13

If you modify the 'StoreProduct' table, so that it has no surrogate primary key, but a primary key which exists of the 2 foreign key columns (ProductId & StoreId), then you can simply limit yourself to 3 entities: - Employee - Product - Store

Your Store class could then have a Set of Products, which can simply be mapped as a many-to-many relationship.

public class Store
{
   public int Id {get;set;}
   public string Name {get;set;}

   public ISet<Product> Products = new HashedSet<Product>();
}

And in the Store.hbm.xml mapping:

<set name="Products" table="StoreProducts">
   <key column="Store_Id" />
   <many-to-many class="Product" column="Product_Id" />
</set>

So, to answer your question: first option is better.

How to best design the entity data classes for the following SQL Schema?

1 Answers