Sql-server – How to represent class table inheritance (current DBMS-specific way please)

database-designsql serversubtypes

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server.

I found a recommendation for MySQL:

How do I map an IS-A relationship into a database?

Basically, it is:

use identical primary keys between the parent table (Account) and the subtype tables (CatAccountDetails, DogAccountDetails)
add a subtype discriminator column to the parent table and the subtype tables
include both the shared primary keys and subtype columns in a foreign key constraint from subtype to parent type tables

Adding a type column that is constrained to a single value (e.g. a 'C' column in CatAccountDetails) feels just a little hackish, so I wondered if there is a feature in SQL Server to enable this scenario?

Also, if this is the way to do it, should I define something additionally to prevent this 'unused' column from degrading performance when my ORM (Entity Framework) requests a table join, like create an additional foreign key on just the PK?

The main problem is that CatAccountDetails will have different columns from DogAccountDetails. Cats have both different and far more properties than dogs in my application. Side note, 'cats' and 'dogs' represent three categories of website members in my current scenario.

Best Answer

Ultimately, I implemented ypercube's suggestion from comments:

The "type" column can be defined as a computed (but constant) column in SQL-Server. I think it has to be PERSISTED though so it can participate in the Foreign Key constraint.

This worked well both for performance and compatibility with with my tools (Entity Framework <= 6.0):

CREATE TABLE [dbo].[Account](
    [Id] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole] [int] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
CONSTRAINT [UX_Derived_Relation] UNIQUE ([Id], [CommunityRole]))

CREATE TABLE [dbo].[Recruiter](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((1)) PERSISTED NOT NULL,
    [RecruiterSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

CREATE TABLE [dbo].[Candidate](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((2)) PERSISTED NOT NULL,
    [CandidateSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

This mapped well to my implementation of multiple discrete account types, Recruiter and Candidate, on my Job Board.

Related Solutions

Sql-server – NULLs in a composite primary key – SQL Server

There is a lot of info missing in your question

Can a pet have multiple owners? eg we have a pet, not I have a pet
Do you expect more pet types (eg Rabbit, Bird, Fish)?

The problem with your current model (as shown) is the Pets table: it adds no value currently. Of course, your simplified design now hides information that probably makes it useful...

I'll throw in some ideas with assumptions though.

Note: This is correct design, whether EF can deal with it or not. That is, you design the model, implement the database, then make your client work with it

Option 1:

Assuming an owner can have 0..n dogs and/or 0..n pets, then you'd need 3 object tables and 2 link tables. You don't need Pet.

Owner, Cat, Dog
Link tables for CatOwner and DogOwner

This allows multiple owners per Dog and Cat. You can of course just have an OwnerID in Dog and Cat to restrict to one owner.

You can have different attributes for Cat and Dog here.

As you add more types, this becomes unwieldy (no matter how you model owners)

Option 2:

Do you need to distinguish Cat or Dog as separate entities? Isn't this just a type attribute of Pet?

**Pet
PetID, PK,
PetType, FK,
PetName
OwnerID, FK (optional, see below)
...

You can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).

This requires each Cat or Dog (or Rabbit, Snake etc) object to have the same attributes.

Option 3:

The final way is superkey/subtype.

This is is useful because if combines elements of options 1 (different attributes per Cat and Dog) and option 2 (keeps the idea of Pet)

This defines one Pet object that has exactly one sub type of Dog, Cat, Rabbit, Snake etc. The super key comes from the extra UQ1 in Pet to enforce subtype. Pet as a table also stores common attributes.

**Pet
PetID, PK, UQ1
PetType, UQ1, 'Dog' or 'Cat'
PetName
OwnerID, FK (optional, see below)
...

**Dog
PetID, PK, FK1
PetType, PK, FK1, constraint = 'Dog'
Breed  -- per Dog,not per Pet
CatsChased
...

**Cat
PetID, PK, FK1
PetType, PK, FK1, constraint = 'Cat'
Breed -- per Cat, not per Pet
MiceEaten
...

Note: UQ1 is a unique constraint used by the FKs in Cat and Dog. It is a "super key" on Pet that allows sub-types via the FK

Again, you can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).

Note, PetType can be a constant derived column in Dog and Cat

Edit: added tables for Pet, Cat and Dog.
I assume Owner is obvious...

CREATE TABLE PetSpecies (
    PetSpeciesID tinyint NOT NULL IDENTITY (1,1),
    Species varchar(100) NOT NULL,

    CONSTRAINT PK_PetSpecies PRIMARY KEY CLUSTERED (PetSpeciesID),
    CONSTRAINT UQ_PetSpecies UNIQUE (Species)
);
INSERT PetSpecies (Species) VALUES ('Dog');
INSERT PetSpecies (Species) VALUES ('Cat');

CREATE TABLE Pet (
    PetID int NOT NULL IDENTITY (1,1),
    PetSpeciesID tinyint NOT NULL,
    PetName varchar(100) NOT NULL
    OwnerID int NOT NULL,
    ...

    CONSTRAINT PK_Pet PRIMARY KEY CLUSTERED (PetID),
    CONSTRAINT FK_Pet_PetSpecies FOREIGN KEY (PetSpeciesID) 
                                 REFERENCES PetSpecies (PetSpeciesID),
    CONSTRAINT FK_Pet_Owner FOREIGN KEY (OwnerID) REFERENCE Owner (OwnerID),
    CONSTRAINT UQ_SuperKey UNIQUE (PetID, PetSpeciesID)
    );

CREATE TABLE Dog (
    PetID int NOT NULL,
    PetSpeciesID tinyint NOT NULL,
    CatsChased int NOT NULL,

    CONSTRAINT PK_Dog PRIMARY KEY CLUSTERED (PetID, PetSpeciesID),
    -- CONSTRAINT UQ_Dog UNIQUE (PetID),
    CONSTRAINT CK_Cat CHECK (PetSpeciesID = 1 /*dog*/),
    CONSTRAINT FK_Dog_Pet FOREIGN KEY (PetID, PetSpeciesID) 
                          REFERENCES Pet(PetID, PetSpeciesID)
    );

CREATE TABLE Cat (
    PetID int NOT NULL,
    PetSpeciesID tinyint NOT NULL,
    MiceEaten int NOT NULL,

    CONSTRAINT PK_Cat PRIMARY KEY CLUSTERED (PetID, PetSpeciesID),
    -- CONSTRAINT UQ_Cat UNIQUE (PetID),
    CONSTRAINT CK_Cat CHECK (PetSpeciesID = 2 /*cat*/),
    CONSTRAINT FK_Cat_Pet FOREIGN KEY (PetID, PetSpeciesID) 
                          REFERENCES Pet(PetID, PetSpeciesID)
    );

Notes:

The PK of cat and dog can be PetID or PetID, PetSpeciesID. The latter is better to match the foreign key.
The unique constraint on cat and dog is optional, really, as the index is contained within the PK and PetID is unique anyway if PetSpeciesID is constrained to a single value.

Postgresql – Need for indexes on foreign keys

Your design is good. If you are having a performance problem (which you can't know at design time), you should create an index on column table1.ref_field, in the same order (ASC) as the table2.id column. This will improve performance on joins between those to tables/columns. There is overhead to maintaining any index, so you want to weigh that cost against the benefit of improved performance.

PostgreSQL doesn't automatically create such indexes on foreign key columns that reference other columns, unfortunately, so you have to do it yourself.

Here is a StackOverflow question on the same topic:

Postgres and Indexes on Foreign Keys and Primary Keys

Here is a query to help determine where you might benefit from adding such an index:

Postgresql: Indexes on Foreign Keys

Best Answer

Related Solutions

Sql-server – NULLs in a composite primary key – SQL Server

Postgresql – Need for indexes on foreign keys

Related Question