I have a very specific question that I am surprised no one has yet asked. It involves a concrete class model and SQL Server 2016 DDL. The main point is:
In my model, some classes inherit from a base class, let's say "BaseInfo"
.
This class is abstract. In SQL terms it means I do not (or do I?) want to create a separate table with rows (eg. CREATE TABLE "BaseInfo"....
. I do however want some of my tables (actually quite a lot of them) to have the same columns (plus some of their own). In OOP terms I want some of my classes to inherit from that table definition. Is there a way to define this in DDL, or do I have to implement this manually?
Notes:
-
I have read all the articles that are to be found on TPH, TPT, TPC. They are too simplistic, they all feature the same Person-Student-Educator Model and do not match my case. What I probably want to do is TPC, but I want changes made to
BaseInfo
table to automatically apply to all child classes. -
I will not be querying
BaseInfo
. I'd rather do a (rare) complex query involving some (or all) of the children. -
Changes made to
BaseInfo
DDL (ALTER TABLE BaseInfo...
) have to be reflected to the children (preferably automatically). -
PostgreSQL shines here with the INHERITS statement. Is there any equivalent in SQL Server (I have done research and found nothing yet)?
-
In case I get no answer or have some other breakthrough, I will go the manual approach. Save a code snippet with the
BaseInfo
DDL and add it to theCREATE TABLE
statement of each child table. -
The answer must respect the conceptual model definition!
Best Answer
While correct that PostgreSQL does indeed support this request, SQL Server has no similar ability. That leaves you with either placing all of the "common" columns in each table, or having a "base" table that contains the common columns that will be joined to when necessary (most likely it will not be required 100% of the time).
I would recommend against copying these "common" columns into all (most) tables as indicated here:
because:
long-term maintenance is more difficult / error prone as there is much greater potential for the schema to get out of synch. Templating the
CREATE TABLE
statements is one thing, but modifications need to be coordinated. In fact, one of the requirements here is:performance will generally be better when separating out the data. Please keep in mind that databases have a fundamentally different goal -- physical storage and set-based operations -- than app code. While it might seem counter-intuitive and/or "clunky" and/or against "architectural" best-practices, structuring a data model to the strengths of the particular RDBMS will yield the best results. Joins might seem to be "extra" work (and sometimes they are), but RDBMSs are specifically optimized to work in this way (smaller tables generally are faster for both querying and index maintenance -- something that shouldn't be ignored!).
you can get around much of the "messiness" of the JOINs by abstracting each Class + SubClass relationship into Views. These can definitely help in all
SELECT
cases, and even in mostUPDATE
cases via Updatable Views (described in detail in the MSDN page for CREATE VIEW.INSERT
andDELETE
statements do not work as well, but theINSERT
can still be made easier by combining both tables via theOUTPUT
clause of theINSERT
into theBaseInfo
table, andDELETE
statements can handle both tables when specifying theON DELETE CASCADE
property on the Foreign Key. Here is an example to show most of this:I have described in greater detail variations of implementing this type of data model in the following DBA.SE answers:
P.S. For what it's worth (and that might not be much ;-), I am not sure how much actual benefit there is to this feature since most of the time, the properties in the "base" class do have value in being queried individually, even if only in reports. This is similar to PostgreSQL's ability to overload functions, which has been recently requested on Microsoft Connect ( User defined function overloading? ) and which, from having worked with it, ended up being more pain than gain.