Should you use single-dimensional inheritance or multiple tables that are combined into a view?

Question

Say you have a table for notes. The note may be about a specific account, ordering order or order.

• Notes related to the account do not apply to any particular order line or order.
• Notes regarding ordering also apply to parental order and the account attached to the order.
• Notes that relate to the order also apply to the attached account, but not to the order line.

NOTE 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 look at a client, I can see all the notes that are related to each other. First, I created a note table for each of the above and combined them in a view. The problem here is with editing / deleting a record. Notes can be edited / deleted by a specific item or as general notes of an account or order. This method has complicated the task.

Then I switched to the Single Inheritance Table template . My notes table is NULL for accounts, OrderId and OrderLineId. I also added NoteTypeId to explicitly identify the entry. It is much easier to manage update / delete scripts.

I have some problems and questions still with this approach.

• . SQL / , STI.
• ( , SQL 2008 )
• . , . , , ?

. " " ?, , , , .

Answer 1

SQL / , STI.

( CHECK) -, - , , .

"--" .

• , ALTER TABLE ( , , , , )
• .
• , ​​ " ".

Answer 2

, STI ?. , :

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

: ? AccountId, OrderId OrderLineId FK'd ( NULL) , , AccountId, OrderId OrderLineId ( BTW!), ObjectId NoteTypeId, RI CASE WHEN. p >

: , AccountId , , , OrderLine Order, (AccountId, OrderId) (AccountId, OrderId, OrderLineId), , ( #OrderLines )

OMG Ponies ALTER TABLEs, , , .

Answer 3

[] .

, NULLable, . " " ( ) UNION.

​​ . NoteTypeId VARCHAR, ( INTEGER):

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)
);

" " (.. JOIN no UNION s):

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;

, . , " ", CHECK , , .

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

, , " ", , ( , , " ", " ":)