Database design - keep record of chained entries

There are 3 cases

1) Basic
Sender ---> Receiver

2) Parallel
Sender ----> Receiver1
       ----> ReceiverN

3) Chained
Sender ----> Primary Receiver  -----> Secondary Receiver1
                               -----> Secondary ReceiverN

For 1) Basic and 2) Parallels, you wou开发者_开发知识库ld probably design your tables like this

Account
-Id (PK)
-UserId (FK)
-Name
-Description
-etc

Entry
-Id (PK)
-SenderAccountId (FK)
-ReceiverAccountId (FK)

Now how would you design the database to record "Chained" entries?

You can add a many to many relationship between Account(Id-PK,UserId-FK,Name,Description,...) and Entry(Id-PK) tables: EntryAccount(EntryId & AccountId-PK,EntryAccountType) where EntryAccountType field can have one of the following values {S=Sender,R=Receiver, P=Primary receiver,N=secoNdary receiver}.

The INSERT statements for EntryAccount table will be:

--Basic
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'S')
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'R')

--Parallel
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'S')
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'R') 
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'R')
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'R')

--Chained
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'S')
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'P')
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'N') 
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'N')
INSERT EntryAccount (EntryId,AccountId,EntryAccountType)
VALUES (...,...,'N')

Then, to enforce some of business rules (one sender-S, one primary receiver-P and many [secondary] receivers-R/N) you can create an unique filtered index(SQL Server 2008) on EntryAccount table: IUF_EntryAccount_EntryId_EntryAccountType(key > EntryId & EntryAccountType, filter > EntryAccountType IN ('S','P')). Also, this index is good for query optimization. But, this index is not enough because you can have "inconsistent" Entry business objects like these:

Entry(1001)
EntryAccoount(1001,...,'S') without EntryAccoount(1001,...,'R') 
or
EntryAccoount(1001,...,'R') without EntryAccoount(1001,...,'S')
, etc.

To correct this problem you need a trigger AFTER INSERT, UPDATE, DELETE on EntryAccount table:

    CREATE TRIGGER ...
    AFTER INSERT, UPDATE, DELETE
    ...
    DECLARE @Results TABLE
    (
    EntryId INT PRIMARY KEY
    ,SendersCount INT NOT NULL DEFAULT O
    ,ReceiversCount INT NOT NULL DEFAULT O
    ,PrimaryReceiversCount INT NOT NULL DEFAULT O
    ,SecondaryReceiversCount INT NOT NULL DEFAULT O
    );
    INSERT @Results(EntryId)
    SELECT EntryId
    FROM inserted
    UNION --no duplicates
    SELECT EntryId
    FROM deleted;

    --Count senders
    UPDATE @Results
    SET SendersCount = q.Num
    FROM @Results r
    JOIN
    (
    SELECT ea.EntryId, COUNT(*) Num
    FROM EntryAccount ea
    JOIN @Results i ON ea.EntryId = i.EntryId
    WHERE ea.EntryAccountType = 'S'
    GROUP BY ea.EntryId
    ) q ON r.EntryId = q.EntryId;

    -Count [standard-R] receivers
    UPDATE @Results
    SET ReceiversCount = q.Num
    FROM @Results r
    JOIN
    (
    SELECT ea.EntryId, COUNT(*) Num
    FROM EntryAccount ea
    JOIN @Results i ON ea.EntryId = i.EntryId
    WHERE ea.EntryAccountType = 'R'
    GROUP BY ea.EntryId
    ) q ON r.EntryId = q.EntryId;

    --Count primary-P receivers
    UPDATE @Results
    SET PrimaryReceiversCount = q.Num
    FROM @Results r
    JOIN
    (
    SELECT ea.EntryId, COUNT(*) Num
    FROM EntryAccount ea
    JOIN @Results i ON ea.EntryId = i.EntryId
    WHERE ea.EntryAccountType = 'P'
    GROUP BY ea.EntryId
    ) q ON r.EntryId = q.EntryId;


    --Count secondary-N receivers
    UPDATE @Results
    SET SecondaryReceiversCount = q.Num
    FROM @Results r
    JOIN
    (
    SELECT ea.EntryId, COUNT(*) Num
    FROM EntryAccount ea
    JOIN @Results i ON ea.EntryId = i.EntryId
    WHERE ea.EntryAccountType = 'N'
    GROUP BY ea.EntryId
    ) q ON r.EntryId = q.EntryId;

    --Final validation
    IF EXISTS
    (
    SELECT *
    FROM @Results r
    WHERE NOT(r.SendersCount=1 AND r.ReceiversCount>=1 AND r.PrimaryReceiver=0 AND r.SecondaryReceiversCount=0 
    OR r.SenderCount=1 AND r.ReceiversCount=0 AND r.PrimaryReceiver=1 AND r.SecondaryReceiversCount >=1
    OR r.SenderCount=0 AND r.ReceiversCount=0 AND r.PrimaryReceiver=0 AND r.SecondaryReceiversCount=0
)
    )
    ROLLBACK;

If you don't have SQL Server 2008 (R1/R2) you cannot create filtered index but you can rely only on trigger.

PS: I have not tested this solution.

Add a parent_record_id to the Entry table. For the first step in the chain, it will be null. For the second, it will contain the ID of the the original record. The third step it will contain the ID of the relevant second step and so on.

I see in the comments you say it can be a maximum of 2 links deep, so you may be able to come up with a different way of solving this with another table, but this way is generic and will work for many scenarios, and continue to work if your requirements change to 3 or more levels of chaining.

You can then lookup the CONNECT BY SQL syntax to do efficient hierarchy queries on this type of data structure.