How to map foreign key attribute tables to a business entity

Question

I have a table in a database that I'm mapping to a main entity class. The table has some columns that hold foreign key values to other attribute tables.

How do I map these to the business classes? Do I create a new class for every attribute table and have the primary class hold these as properties?

Answer 1

If the relationship between main table and attribute table is 1 : 1 (there is one, or only one active record in attribute table for a given record in main table) I would have a property for every attribute in attribute table(s).

If the relationship between main table and attribute table is 1 : n, I would have a separate class (maybe nested in the main entity class) for each attribute table. Main entity class would then have a property for each attribute table, which would be a list of type List<AttributeType> (since there can be many records in attribute table for a given record in main table).

Answer 2

I think you're on the right track with your main "entity" class having properties for those relationships that are of another strong type.

Assume you have a Product table and a Category table, with the Product table containing a foreign key to the Category table's primary key. In your code, your Product class doesn't get an integer "CategoryID" property - it gets a "Category" property which is a reference to a Category type.

class Product
{
  public int ProductID { get; set; }
  public string Name { get; set; }
  public Category Category { get; set;}
}

class Category
{
  public int CategoryID { get; set; }
  public string Name { get; set; }
}

Going this route lets you code your business logic using standard classes and then your persistence layer (EF, NHibernate, etc) can deal with the database interaction and handling the foreign keys between the tables.