What would the database schema look like for a Entity Component System architecture of web app?

Question

I found previous SE questions like this.

I'm familiar with the typical RDBMS backed MVC web app framework. To illustrate what I am thinking of, let's use the examples of Bunny and Carrot from this [article].

In a typical MVC

For a typical MVC RDBMS, you would have two tables. A carrots table and a bunnies table. Which is each associated with a Carrot model class and a Bunny model class.

What if we do this in a ECS way?

What would the database schema look like if we do it in ECS way?

Here's my speculation.

First, we would have each component become separate tables.

so we would have the following tables:

• placeables with the fields x, y, z
• huggables with the fields fluffiness
• seeings with the fields sight_radius, night_vision
• livings with the fields health, age
• physicals with the fields height, width, length
• consumables with the fields calories
• hoppings with the fields hop_distance

And in all these tables they will have an entity_id as foreign key.

There will also be a entities, entity_types and component_types table

So Bunny is an Entity Type which means it's represented as a row inside entity_types Same as Carrot.

A specific Bunny will be an actual entity with the id 1 and entity_type_id as foreign key in the entities table.

What we typically do of establishing the fields of a Bunny in a MVC way would be to put the fields inside the bunnies table.

Here we simply have a relation table entity_type_to_component_types where the Entity Type Bunny is related to the specific Component Types

Now let's talk about the specific CRUD action

All the read operations will be join heavy.

They will involve joining all the individual component tables referencing the entity_id.

CUD are pretty straight forward and in fact might be better.

Relations between entities?

Typically a relation between entities in the MVC is simply a relation table.

Here, we can simply establish a specific relation as another component, say One Bunny eats Many Carrots then the component is carrot_eaten_by_bunny which contains the specific carrot, and the specific bunny ids

My question is: is this implementation sound? What drawbacks do I not anticipate?

Is it just the huge number of joins when doing reads? How about creating virtual tables or views if reading speed is the issue?

Are there actually implementations like this in the real world I'm not aware of?

Answer 1

This approach is very unsound, because it completely ignores the role of the System aspect of ECS. There's not a single mention of systems in the question.

The way the state is sliced and diced — not just in ECS, but pretty much everywhere else — depends on its usage. If two pieces of data always come together, it means said pieces belong in a single component — or a class, a struct, an interface, a record, a table, etc.

In ECS, any and all state manipulation is happening in the systems. (Except when the state is transferred outside of ECS's bounds for e.g. savegames, but that's irrelevant.) Entities and components are data only, no business logic allowed.

Therefore, without defining the systems, any decision on the component design would be a guesswork.

(Of course ECS is not as onerous as TDD, there is no pressure to design systems-first, although this should rarely be a problem, because even a small game requires so many things up front, which makes the approximate schema very foreseeable.)

---

In your example, the components are defined to your taste. It may be a horrific case of over-engineering, or it may be a state of the art for a given problem — either is possible.

If you'd ask me to play a guessing game, I'd say that you can't have more than 3 components for 2 pieces of data: 1 for the common, 2 for the difference. Moreover, given that a carrot has no unique components, 2 components must be enough: Food and Bunny.

But that's only because I focus on a database performance. If your system is going to spend most of it's cycles on sorting food based on it's calorie content (Consumable in, Placeable out), my design would end up substantially inferior to yours.