.net - How do you effectively model inheritance in a database? -

what best practices modeling inheritance in databases?

what trade-offs (e.g. queriability)?

(i'm interested in sql server , .net, want understand how other platforms address issue.)

there several ways model inheritance in database. choose depends on needs. here few options:

table-per-type (tpt)

each class has own table. base class has base class elements in it, , each class derives has own table, primary key foreign key base class table; derived table's class contains different elements.

so example:

class person {     public int id;     public string firstname;     public string lastname; }  class employee : person {     public datetime startdate; } 

would result in tables like:

table person ------------ int id (pk) string firstname string lastname  table employee -------------- int id (pk, fk) datetime startdate 

table-per-hierarchy (tph)

there single table represents inheritance hierarchy, means several of columns sparse. discriminator column added tells system type of row is.

given classes above, end table:

table person ------------ int id (pk) int rowtype (0 = "person", 1 = "employee") string firstname string lastname datetime startdate 

for rows rowtype 0 (person), startdate null.

table-per-concrete (tpc)

each class has own formed table no references off other tables.

given classes above, end these tables:

table person ------------ int id (pk) string firstname string lastname  table employee -------------- int id (pk) string firstname string lastname datetime startdate