table inheritance

In this article I'm going to lay down the foundation for the Nordic Object/Relational Database Design, using cascading custom class tables.  This particle pattern uses a concrete table for each class. 

I learned of this design many years ago through <a href="http://www.sqlserverbible.com/">Paul Nielsen's</a> article <a href="http://msdn.microsoft.com/en-us/library/bb245675.aspx">The Nordic Object/Relational Database Design</a> which is an excellent read for the gritty details.

The heart of the cascading custom class pattern is a base table and a type table.  The type table will have an entry for each concrete class type (think leaves of the inheritence graph).  The base table stores the type and provides the identity ID used for the one-to-one relationship that join the records across the tables in the inheritence graph of a class's records.  The type diambiguates the class type of the root and node tables of the inheritence graph can be of different types.

The names of tables are arbitrary, in this case item and itemType.  Here's the bootstrap for these two tables:

/* caption is not required but provides a common place to store a human friendly representation */
create table dbo.item (
    id bigint identity(1,1) primary key,
    itemTypeId bigint,
    caption varchar(255) not null
);

create table dbo.itemTypeAbstract (
    id bigint primary key,
    schema_name sysname,
    table_name sysname,
    constraint [fk_itemType_item_id] foreign key (id) references dbo.item(id)
)

insert into dbo.item (caption, itemTypeId) values ('Item Type', 1);
insert into dbo.itemTypeAbstract (id, schema_name, table_name) values (1, 'dbo', 'itemType');

alter table dbo.item
add constraint [fk_item_itemType] foreign key (itemTypeId) references dbo.itemType(id);

go 

Table 'itemType' itself is a subclass of 'item'.  Note 'caption' in root table dbo.item;  All sub-class tables will inherit this field (along with id and itemTypeId).

As of now it takes two inserts to get any values into the concrete class itemType (a row in item and a row in itemType).  The proper set of 'instead of' triggers will allow the view to become fully CRUDable, but first a view is needed:

create view dbo.itemType with schemabinding as
select
    item.id, item.typeId, item.caption,
    itemType.schem_name, itemType.table_name
from
    dbo.itemTypeAbstract as itemType
    inner join dbo.item as item on item.id = itemType.id

create unique clustered index on dbo.itemType (id);

You may or may not want the clusterd index;  If operations are read heavy, the clustered index will materialize the view and make reads much faster at the cost of more expensive DML actions. 

select * from dbo.itemType;

id	typeId	caption	schema_name	table_name
1	1	Item Type	dbo	itemType

I'll cover the instead of triggers in a future article;  These triggers provide the insert, update, and delete directly against the view.