0

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 entities is not constant between entities.

Example:

Car has an engine, the engine have multiple components which may comprise multiple parts and the parts my have smaller parts

As stated above, the number of subsequent child entities cascading down is different for instance between a Train and a Car, so I cannot create indefinite child tables for each parent.

My question is, what is the most efficient way to store such data and maintain a relationship between each parent and subsequent child? An example will be much appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
NCS
  • 43
  • 3
  • 1
    For parent / child relationships you frequently need only a single self referencing table with ID, and either a ParentID or ChildID column (along with other metadata columns). More important here is how you are going to consume the data, which we can't really advise you on without more specifics. – squillman Feb 11 '20 at 18:17
  • I understand creating a single self reference table and the parent child relationship. But how do I track or maintain the relationship if the child becomes a parent to another child and so forth. See example mentioned. I have an idea of how I will consume the data, but for now it is storing it in the most efficient way without creating a indefinite chain of parent / child tables. I should also mention that the meta data for subsequent child tables is the same. Not sure I understood you correctly, but perhaps you could share a simple example. – NCS Feb 11 '20 at 18:37
  • 1
    One table like `entity_id | parent_entity_id` will suffice. When you have a new child come in, you just add a new record noting which `parent_entity_id` it belongs to. When you want to pull the all of the descendants of an entity you use a Recursive CTE. This way it's one table and scales. If the attributes of these entity (`color`, `create_date`, `smell`, `country`, whatever) are all the same then you can add them as columns in this table as well. – JNevill Feb 11 '20 at 19:21
  • Thanks. This was the way I thought of doing it, but I was not sure if it would be the most efficient way, hence my question. Help much appreciated. – NCS Feb 12 '20 at 05:12

1 Answers1

2

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.

devio
  • 36,858
  • 7
  • 80
  • 143