How to model Parent -> Child -> Grandchild if the Child can be removed I'm dealing with a set of relationships where the child can be removed, but I obviously don't want to lose the connection between the grandchild and the parent. I did think about marking the child as "deceased" (to use relevant terminology for this post), but then I would be stuck with a bunch of deceased children in my DB eventually and who wants that (just for the purpose of preserving the relationship)?



If a parent is deleted, all its descendants are deleted. Furthermore, it works like a "normal" relationship, where the grandchild and child always have the same top level parent. The hierarchy is fixed in 3 levels (as shown above). Finally, the Parent, Child, and Grandchild are all of different types (e.g. we're not talking about 3 "humans", they don't have the same base).

However, it feels a bit odd to have the grandchild track the parent since that relationship can usually be derived from the parent-child relationship. Though, I cannot think of another way of doing it.

Is this model valid? Or is there a different way of doing it?
This answer is based upon your question as it stood before the clarification about each level being a different type.   Since you have identified the need for different types, I agree with my answer as it originally appeared, and your self-answer documenting how you've approached this problem.  Adding a single column to the grandchild table, referencing the top-most table, seems the simplest approach.

I'm leaving the below details in-place in case it helps a future visitor.



I would implement this with a cross reference table.  

Below is a SQL Server-specific example; this table contains columns about the entities, including name, etc:

CREATE TABLE dbo.Entities
(
    EntityID int NOT NULL
        CONSTRAINT PK_Entities
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , EntityName varchar(30) NOT NULL
);


This table describes their relationships:

CREATE TABLE dbo.EntityRelationships
(
      EntityIDParent int NOT NULL
        CONSTRAINT FK_EntityRelationships_Parent
        FOREIGN KEY
        REFERENCES dbo.Entities (EntityID)
    , EntityIDChild int NOT NULL
        CONSTRAINT FK_EntityRelationships_Child
        FOREIGN KEY
        REFERENCES dbo.Entities (EntityID)
    , CONSTRAINT PK_EntityRelationships
        PRIMARY KEY CLUSTERED (EntityIDParent, EntityIDChild)
    , CONSTRAINT CK_EntitytRelationships
        CHECK ((EntityIDParent &lt;&gt; EntityIDChild))
);


Each relationship must be unique, that is any given parent can only be related to any given child once.

Next, we create an INSTEAD OF DELETE trigger on the Entities table that will handle deletes properly, by reparenting any relationships necessary, prior to removing the deleted Entity:

CREATE TRIGGER EntityRelationshipDelete
ON dbo.Entities
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
    SELECT erp.EntityIDParent
        , erc.EntityIDChild
    FROM deleted d
        INNER JOIN dbo.EntityRelationships erp ON d.EntityID = erp.EntityIDChild
        INNER JOIN dbo.EntityRelationships erc ON d.EntityID = erc.EntityIDParent
    EXCEPT --don't create duplicate entries
    SELECT er.EntityIDParent, er.EntityIDChild
    FROM dbo.EntityRelationships er;

    DELETE
    FROM dbo.EntityRelationships 
    FROM dbo.EntityRelationships er
        INNER JOIN deleted d ON er.EntityIDChild = d.EntityID OR er.EntityIDParent = d.EntityID;

    DELETE 
    FROM dbo.Entities
    FROM dbo.Entities e
        INNER JOIN deleted d ON e.EntityID = d.EntityID;
END;
GO


Here we'll test that setup:

INSERT INTO dbo.Entities (EntityName)
VALUES ('Grandparent')
    , ('Parent')
    , ('Child');

INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
VALUES (1, 2)
    , (2, 3);

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;


The results of the select above:

╔═════════════╦════════════╗
║ EntityName  ║ EntityName ║
╠═════════════╬════════════╣
║ Grandparent ║ Parent     ║
║ Parent      ║ Child      ║
╚═════════════╩════════════╝

Here, we'll delete the "Parent" entity, and re-query the relationships:

DELETE 
FROM dbo.Entities
WHERE dbo.Entities.EntityName = 'Parent';

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;


The result:

╔═════════════╦════════════╗
║ EntityName  ║ EntityName ║
╠═════════════╬════════════╣
║ Grandparent ║ Child      ║
╚═════════════╩════════════╝

Note that running DELETE FROM dbo.Entities (with no WHERE clause) will delete all rows from both tables.

To show a slight more complex example; imagine you have 2 grandparents, 2 parents, and single child:

INSERT INTO dbo.Entities (EntityName)
VALUES ('Grandparent 1')
    , ('Grandparent 2')
    , ('Parent 1')
    , ('Parent 2')
    , ('Child');

INSERT INTO dbo.EntityRelationships (EntityIDParent, EntityIDChild)
VALUES (1, 3)
    , (2, 3)
    , (1, 4)
    , (3, 5)
    , (4, 5);

SELECT Parents.EntityName
    , Children.EntityName
FROM dbo.EntityRelationships er
    INNER JOIN dbo.Entities Parents ON er.EntityIDParent = Parents.EntityID
    INNER JOIN dbo.Entities Children ON er.EntityIDChild = Children.EntityID;


╔═══════════════╦════════════╗
║  EntityName   ║ EntityName ║
╠═══════════════╬════════════╣
║ Grandparent 1 ║ Parent 1   ║
║ Grandparent 1 ║ Parent 2   ║
║ Grandparent 2 ║ Parent 1   ║
║ Parent 1      ║ Child      ║
║ Parent 2      ║ Child      ║
╚═══════════════╩════════════╝

If we remove Parent 1 from the Entities table:

DELETE 
FROM dbo.Entities
WHERE dbo.Entities.EntityName = 'Parent 1';


we see this:

╔═══════════════╦════════════╗
║  EntityName   ║ EntityName ║
╠═══════════════╬════════════╣
║ Grandparent 1 ║ Parent 2   ║
║ Grandparent 1 ║ Child      ║
║ Grandparent 2 ║ Child      ║
║ Parent 2      ║ Child      ║
╚═══════════════╩════════════╝

This performs a cleanup of our test data:

IF OBJECT_ID(N'dbo.EntityRelationships', N'U') IS NOT NULL
DROP TABLE dbo.EntityRelationships;

IF OBJECT_ID(N'dbo.Entities', N'U') IS NOT NULL
DROP TABLE dbo.Entities;
GO