Create A Sql Table That Can Have Cascading Child Parent Relationships

I have a set of entities that can have child entities which themselves have child entities and they have child entities .... etc. The problem is that the number of subsequent child

Solution 1:

The simplest data model would be a tree like this:

TABLE Entity (Id, Name, EntityTypeId, ParentEntityId NULL)
TABLE EntityType (Id, Name, ParentEntityTypeId NULL)

In a more complex model, e.g. different car models sharing the same motor model (and it's not clear whether you are talking about car models, or specific cars), the ParentEntityId column would be replaced by a relation table:

TABLE Entity (Id, Name, EntityTypeId)
TABLE EntityHierarchy (Id, ParentEntityId, ChildEntityId)

The entries in the EntityHierarchy table would be constrained (on application level) by the tree of EntityTypes

In a more complex model, the ParentEntityTypeId would also be replaced by a hierarchy table.

If your entities or their types change over time, you would also add a DATE range to any of these tables.