views:

52

answers:

0

Given the following tables:

Product
-----------
ProductId : int (PK)
ProductVersion : int

ProductHistory
-----------
ProductId : int       (PK)
ProductVersion : int  (PK)

Item
-----------
ItemId : int (PK)
ProductId : int      (FK) -- ProductId + ProductVersion relates to ProductHistory
ProductVersion : int (FK)

And the following classes:

public class Product { }
public class Item {
    public Product Product { get; set; }
}

What I want to happen is this; we get a Product from the Product table, assign it to Item.Product property. But that Item.Product property should map to ProductHistory. The idea is that only the latest version of a product is in the main Product table, so we allow customers to search against that table (so that if each product has 4 versions and there are 1000 products, we only need to query though 1000 products, not 1000 products * 4 versions of each).

Any idea how to acomplish this?

Thanks Andy