I am trying to understand the inheritance mappings in EF4.
My database has two tables with the following structure:
PersonCategory Table:
- CategoryID (int) (identity) (PK)
- CategoryType (nvarchar(50))
Person Table
- PersonID (int) (identity) (PK)
- CategoryID (FK from PersonCategory table)
- Name (nvarchar(50))
- Description (nvarchar(max))
PersonCategory table has four entries each representing a category - Student, CourseInstructor, Staff and Advisor.
From reading articles online I thought Table Per Hierarchy will be a suitable model for this scenario. So in EF4, I created four entities(Student, CourseInstructor, Staff and Advisor) each inheriting from Person table. I then mapped each of them to the Person table and added a condition to each (eg. CategoryID = 1 for Student entity and CategoryID = 2 for Staff entity) to differentiate from others. I also removed the CategoryID property from Person table and made it abstract class. But I am getting the following error because I deleted the CategoryId property from Person table.
Error 3015: Problem in mapping fragments starting at lines 101, 108, 114, 120, 126, 133:Foreign key constraint 'FK_Person_PersonCategory' from table Person (CategoryID) to table PersonCategory (CategoryID):: Insufficient mapping: Foreign key must be mapped to some AssociationSet or EntitySets participating in a foreign key association on the conceptual side.
Is Table Per Hierarchy a suitable model for this scenario? If not then how should I approach this scenario in EF4?