Table foreign key / association to itself?

Question

Hi,

I've got a table that stores inventory for a store that looks like this

InventoryId    int
ParentId       int
ShortDesc      int
...
[other product data]
...

A TShirt will exist in the table with a ParentId of -1. Any variations of size and colour will exist in the same table with the original parent InventoryID in the ParentID field.

EG

InventoryID   |ParentID   |ShortDesc
----------------------------------------------------    
111            -1          Skull TShirt
112            111         Skull TShirt Black Small
113            111         Skull TShirt Black Medium
114            111         Skull TShirt Black Large
115            111         Skull TShirt White Small

Is it at all possible that I could specify some kind of foreign key or association where when I use this DB with the Entity Framework I can have a Navigation property on this Entity that will list all of an Inventory Entity's child Inventory?

Answer 1

Yes, this works just fine. For a real-world example, make a model of the Northwind database with default settings, and look at the employee supervisor relationship. The Entity Framework will not automatically get the Supervisor property name right, but the Association itself works, and you can rename the property manually.

Regarding your specific example, I am suspicious of the -1. If this is a real primary key of a real inventory record, fine. If it's a magic value indicating no record, then either use NULL or make it a real record. In order to get the Entity Framework to recognize this relationship, you're going to need to make a real database foreign key, so all values must actually refer to records.