Should you use single table inheritance or multiple tables that are union-ed in a view?

Let's say you have a notes table. The note can be about a particular account, orderline or order.

• Notes that are about the account do not apply to any specific orderline or order.
• Notes that are about the orderline also apply to the parent order and the account that is attached to the order.
• Notes that are on the order also apply to the attached account but not the orderline.

NOTES table

[Id]          [int] IDENTITY(1,1) NOT NULL
[NoteTypeId]  [smallint] NOT NULL
[AccountId]   [int] NULL
[OrderId]     [int] NULL
[OrderLineId] [int] NULL,
[Note]        [varchar](300) NOT NULL

The idea is that if I view a client I can see all notes that are in someway related. Initially I created a notes table for each of the above and union-ed them in a view. The problem here comes with editing/deleting a record. Notes can be edited/deleted on the particular item or in a generic notes view of the account or order. This method made that more difficult.

Then I switched to the Single Table Inheritance pattern. My notes table has nullable values for AccountId, OrderId and OrderLineId. I also added the NoteTypeId to identify the record explicitly. Much easier to manage update/delete scenarios.

I have some problems &开发者_JAVA技巧 questions still with this approach.

• Integrity - Although complex constraints can be set in SQL and/or in code, most DBAs would not like the STI approach.
• The idea of bunch of nulls is debated (although I believe performance in SQL 2008 has improved based on the storage of null values)
• A table in a RDBMS does not have to represent an object in code. Normalization in a table doesn't say that the table has to be a unique object. I believe the two previous sentences to be true, what say you?

Discussed some here. Is an overuse of nullable columns in a database a "code smell"? I'd have to say I agree with Ian but I'd like some opposite views as well.

Although complex constraints can be set in SQL and/or in code, most DBAs would not like the STI approach.

Because you need additional logic (CHECK constraint or trigger) to implement the business rule that a note refers to only one of the entities - account, order, orderline.

It's more scalable to implement a many-to-many table between each entity and the note table.

• There's no need for an ALTER TABLE statement to add yet another nullable foreign key (there is a column limit, not that most are likely to reach it)
• A single note record can be associated with multiple entities
• No impact to existing records if a new entity & many-to-many table is added

It seems the STI would work OK in your case?. If I read your requirements correctly, the entity inheritance would be a chain:

Note <- AccountNote(AccountId) <- AccountAndOrderNote(OrderId) <-AccountAndOrderAndOrderLineNote (OrderLineId)

Integrity: Surely not an issue? Each of AccountId, OrderId and OrderLineId can be FK'd to their respective tables (or be NULL) If on the other hand, if you removed AccountId, OrderId and OrderLineId (I'm NOT recommending BTW!) and instead just ObjectId and NoteTypeId, then you couldn't add RI and would have a really messy CASE WHEN type Join.

Performance: Since you say that AccountId must always be present, I guess it could be non-null, and since OrderLine cannot exist without Order, an Index of (AccountId, OrderId) or (AccountId, OrderId, OrderLineId) seems to make sense (Depending on selectability vs narrowness tradeoffs on the average #OrderLines per Order)

But OMG Ponies is right about messy ALTER TABLEs to extend this to new note types, and the indexing will cause headaches if new notes aren't Account-derived.

HTH

Initially I [created a] separate notes table for each of the above and union-ed them in a view.

This makes me wonder if you've considered using multi-table structure without NULLable columns where each note gets a unique ID regardless of type. You could present the data in the 'single table inheritance' (or similar) in a query without using UNION.

Below is a suggested structure. I've changed NoteTypeId to a VARCHAR to make the different types clearer and easier to read (you didn't enumerate the INTEGER values anyhow):

CREATE TABLE Notes
(
 Id INTEGER IDENTITY(1,1) NOT NULL UNIQUE, 
 NoteType VARCHAR(11) NOT NULL
    CHECK (NoteType IN ('Account', 'Order', 'Order line')), 
 Note VARCHAR(300) NOT NULL, 
 UNIQUE (Id, NoteType)
);

CREATE TABLE AccountNotes
(
 Id INTEGER NOT NULL UNIQUE, 
 NoteType VARCHAR(11) 
    DEFAULT 'Account' 
    NOT NULL
    CHECK (NoteType = 'account'),
 FOREIGN KEY (Id, NoteType)
    REFERENCES Notes (Id, NoteType)
       ON DELETE CASCADE, 
 AccountId INTEGER NOT NULL
    REFERENCES Accounts (AccountId)
);

CREATE TABLE OrderNotes
(
 Id INTEGER NOT NULL UNIQUE, 
 NoteType VARCHAR(11) 
    DEFAULT 'Order'
    NOT NULL
    CHECK (NoteType = 'Order'),
 FOREIGN KEY (Id, NoteType)
    REFERENCES Notes (Id, NoteType)
       ON DELETE CASCADE, 
 OrderId INTEGER NOT NULL
    REFERENCES Orders (OrderId)
);

CREATE TABLE OrderLineNotes
(
 Id INTEGER NOT NULL UNIQUE, 
 NoteType VARCHAR(11) 
    DEFAULT 'Order line'
    NOT NULL
    CHECK (NoteType = 'Order line'),
 FOREIGN KEY (Id, NoteType)
    REFERENCES Notes (Id, NoteType)
       ON DELETE CASCADE, 
 OrderLineId INTEGER NOT NULL
    REFERENCES OrderLines (OrderLineId)
);

To present the data in the 'single table inheritance' structure (i.e. all JOINs and no UNIONs):

SELECT N1.Id, N1.NoteType, N1.Note, 
       AN1.AccountId, 
       ON1.OrderId, 
       OLN1.OrderLineId
  FROM Notes AS N1
       LEFT OUTER JOIN AccountNotes AS AN1
          ON N1.Id = AN1.Id
       LEFT OUTER JOIN OrderNotes AS ON1
          ON N1.Id = ON1.Id
       LEFT OUTER JOIN OrderLineNotes AS OLN1
          ON N1.Id = OLN1.Id;

Consider that the above structure has full data integrity constraints. To do the same using the 'single table inheritance' structure would require many more CHECK constraints with many, many conditions for nullable columns e.g.

CHECK (
       (
        AccountId IS NOT NULL
        AND OrderId IS NULL
        AND OrderLineId IS NULL
       )
       OR
       (
        AccountId IS NULL
        AND OrderId IS NOT NULL
        AND OrderLineId IS NULL
       )
       OR
       (
        AccountId IS NULL
        AND OrderId IS NULL
        AND OrderLineId IS NOT NULL
       )
      );

CHECK (
       (
        NoteType = 'Account'
        AND AccountId IS NOT NULL
       )
       OR
       (
        NoteType = 'Order'
        AND OrderId IS NOT NULL
       )
       OR 
       (
        NoteType = 'Order line'
        AND OrdereLineId IS NOT NULL
       )
      );

etc etc

I'd wager that most application developers using 'single table inheritance' would not be bothered to create these data integrity constraints if it occurred to them to do so at all (that's not meant to sound rude, just a difference in priorities to us who care more about the 'back end' than the 'front end' :)