What you're referring to is called a category structure in data modeling- (complete or incomplete) - sometimes the defining attributes for category structures are referred to as category discriminants. These are structures that define a conditional relationship between one table and one from asset of other tables based on a value in the first table.

I don't personally know if there exists a way to enforce category structures declaratively in MS SQL - I don't think ANSI SQL 92 (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) implements a declarative statement that would make this possible without triggers.

Best,

Juan Cristián


MS Sql Server LazyDBA home page