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:
- 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 the CREATE TABLE statement of each child table.
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:
- Changes made to
BaseInfo DDL (ALTER TABLE BaseInfo...) have to be reflected to the children (preferably automatically).
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 most UPDATE cases via Updatable Views (described in detail in the MSDN page for CREATE VIEW. INSERT and DELETE statements do not work as well, but the INSERT can still be made easier by combining both tables via the OUTPUT clause of the INSERT into the BaseInfo table, and DELETE statements can handle both tables when specifying the ON DELETE CASCADE property on the Foreign Key. Here is an example to show most of this:
/*
DROP VIEW dbo.UpdatableView;
DROP TABLE dbo.UpdatableViewTableB;
DROP TABLE dbo.UpdatableViewTableA;
*/
CREATE TABLE dbo.UpdatableViewTableA
(
ID INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_UpdatableViewTableA] PRIMARY KEY,
IsActive BIT NOT NULL,
InsertTime DATETIME2 NOT NULL CONSTRAINT [DF_UpdatableViewTableA_InsertTime]
DEFAULT (SYSDATETIME())
);
CREATE TABLE dbo.UpdatableViewTableB
(
ID INT NOT NULL CONSTRAINT [PK_UpdatableViewTableB] PRIMARY KEY,
Whateva NVARCHAR(4000) NULL,
CONSTRAINT [FK_UpdatableViewTableB_UpdatableViewTableA_ID]
FOREIGN KEY ([ID])
REFERENCES dbo.UpdatableViewTableA ([ID])
ON DELETE CASCADE
);
GO
CREATE VIEW dbo.UpdatableView
AS
SELECT a.[ID], a.[IsActive], a.[InsertTime], b.[Whateva]
FROM dbo.UpdatableViewTableA a
INNER JOIN dbo.UpdatableViewTableB b
ON b.[ID] = a.[ID];
GO
INSERT INTO dbo.UpdatableViewTableA ([IsActive]) VALUES (1);
INSERT INTO dbo.UpdatableViewTableB ([ID], [Whateva]) VALUES (1, N'test row');
INSERT INTO dbo.UpdatableViewTableA ([IsActive]) VALUES (1);
INSERT INTO dbo.UpdatableViewTableB ([ID], [Whateva]) VALUES (2, N'another row');
SELECT * FROM dbo.UpdatableView;
UPDATE uv
SET uv.IsActive = 0
FROM dbo.UpdatableView uv
WHERE uv.[ID] = 2;
SELECT * FROM dbo.UpdatableView;
UPDATE uv
SET uv.[Whateva] = N'what?'
FROM dbo.UpdatableView uv
WHERE uv.[ID] = 1;
SELECT * FROM dbo.UpdatableView;
DELETE uv
FROM dbo.UpdatableView uv
WHERE uv.[ID] = 1;
-- Msg 4405, Level 16, State 1, Line 59
-- View or function 'uv' is not updatable because the modification
-- affects multiple base tables.
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.
