Introduction
One of the challenges you are confronted with when storing objects to a relational table structure is how to deal with inheritance.
There are three main inheritance mapping strategies (the link in brackets shows a respective example from Wikipedia.org):
- Table per class hierarchy (Single Table Inheritance): A single table is created and for each attribute in the class hierarchy, a column is created on this table.
- Table per subclass (Class Table Inheritance): A table is created for each class. A column for each attribute of the class (not including inherited attributes) is created. A foreign key column is defined referencing the table of the parent class.
- Table per concrete class (Concrete Table Inheritance): A table is created for each class. For each attribute of the class (including the inherited ones), a column is created.
For a more detailed description, also see http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch10.html.
Not all O/R mapping frameworks do however natively support mapping strategies and more than often, an O/R mapping framework is not used at all for whatever reason. It is still not unusual that developers need to manually maintain the object to relational schema mapping.
This tip shows how instead of triggers can be used to make this task easier.
Background
From a database developer's point of view, table per class hierarchy and table per concrete class have some serious disadvantages:
- Table per class hierarchy:
Not Null
constraints cannot effectively be used as columns for attributes that are not in the base class, all have to be declared as nullable. As for each attribute that exists in the class hierarchy in any class, a column has to be set up, the table can become quite large with many columns not populated depending on what object is stored in a given row. - Table per concrete class: For all attributes of a parent class, the same columns (with possibly the same constraints) have to be created on each table that holds objects of the derived class. Primary key constraints over all objects in the class hierarchy is not possible as they are distributed over several tables.
Database developers therefore tend to prefer a table per subclass strategy as this strategy avoids redundant definition of columns and leads to more streamlined table schema.
From an OO developer's perspective, the table per subclass however is the most difficult to work with as it means that an object gets distributed over more than one table.
The solution presented here uses the approach to present a table per table per concrete class view on the outside (e.g. to the OO developer) but use a table per subclass on the inside. The translation between the two is taken care of by an instead of trigger. Unlike most approaches where the O/R mapping logic is fully maintained in the OO layer, this approach moves some of it to the database.
Using the Code
Instead of triggers are executed instead of the triggering action as described per below:
INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints, so can perform preprocessing that supplements the constraint actions.
(See http://technet.microsoft.com/en-us/library/aa214491%28v=sql.80%29.aspx)
They may also be defined on views to propagate table operations that would modify data in more than one underlying table. This technique will serve as the base.
To demonstrate this, we will assume a class hierarchy as per below which should be mapped to a relational Table schema.
To map these, we will first set up the below tables for Person
and Employee
which implement the table per subclass approach.
CREATE TABLE tblPerson
(
ID int PRIMARY KEY,
Name varchar(10),
)
GO
CREATE TABLE tblEmployee
(
ID int PRIMARY KEY,
Department varchar(10),
CONSTRAINT FKEmpPer FOREIGN KEY (ID)
REFERENCES tblPerson (ID) ON DELETE CASCADE ON UPDATE CASCADE
)
GO
Based on these tables, we then set up the views which implement the table per concrete class approach.
CREATE VIEW Person AS
SELECT ID, Name
FROM tblPerson
GO
CREATE VIEW Employee AS
SELECT P.ID as ID, P.Name, E.Department
FROM tblPerson P
JOIN tblEmployee E ON P.ID = E.ID
GO
What is missing are the instead of triggers to propagate modifications from the views to the underlying tables which is the translation between the table per concrete class and table per subclass approach.
CREATE TRIGGER tiu_io_Employee ON Employee
INSTEAD OF INSERT, UPDATE
AS
BEGIN
MERGE tblPerson AS t
USING (SELECT ID, Name from inserted) AS s (ID, Name)
ON (t.ID = s.ID)
WHEN MATCHED THEN
UPDATE SET Name = s.Name
WHEN NOT MATCHED THEN
INSERT (ID, Name)
VALUES (s.ID, s.Name);
MERGE tblEmployee AS t
USING (SELECT ID, Department from inserted) AS s (ID, Department)
ON (t.ID = s.ID)
WHEN MATCHED THEN
UPDATE SET Department = s.Department
WHEN NOT MATCHED THEN
INSERT (ID, Department)
VALUES (s.ID, s.Department);
END
GO
CREATE TRIGGER td_io_Employee ON Employee
INSTEAD OF DELETE
AS
BEGIN
DELETE tblPerson
WHERE ID IN (SELECT ID FROM deleted)
END
GO
We are now ready to run some modifications to demonstrate how the link between views and tables work.
INSERT Employee (ID, Name, Department)
VALUES (1, 'Peter', 'IT')
GO
INSERT Employee (ID, Name, Department)
VALUES (2, 'Susan', 'Finance')
GO
INSERT Person (ID, Name)
VALUES (3, 'Guy')
GO
SELECT * FROM Person
SELECT * FROM Employee
GO
ID Name
1 Peter
2 Susan
3 Guy
(3 row(s) affected)
ID Name Department
1 Peter IT
2 Susan Finance
(2 row(s) affected)
UPDATE Employee
SET Name = 'Sue', Department = 'Accounting'
WHERE ID = 2
GO
SELECT * FROM Person
SELECT * FROM Employee
GO
ID Name
1 Peter
2 Sue
3 Guy
(3 row(s) affected)
ID Name Department
1 Peter IT
2 Sue Accounting
(2 row(s) affected)
DELETE Employee
GO
SELECT * FROM Person
SELECT * FROM Employee
GO
ID Name
3 Guy
(1 row(s) affected)
ID Name Department
(0 row(s) affected)
DELETE Person
GO
SELECT * FROM Person
SELECT * FROM Employee
GO
ID Name
(0 row(s) affected)
ID Name Department
(0 row(s) affected)
As the examples above show, the trigger processes correctly inserts, updates, deletes done to the views. These views can be used as the interface to an OO layer that manipulates data in the database through these views.
Points of Interest
The O/R mapping approach presented here is quite straightforward but requires some coding effort as for
each derived class a view and a set of triggers need to be set up.
Using views as the interface to an OO layer do however offer some advantages: The views can be designed to correspond to the class hierarchy and the names of views and columns in the view can be chosen to exactly match the class and attribute names in the OO layer. This would allow the usage of more simple and less complex frameworks to produce the SQL to be sent to the database. Furthermore, the database table schema underlying the views can be designed relatively independently from the OO model making it relatively easy to construct an OO layer on top of an existing database table schema.
History
- 1st March, 2014: Initial version