1
\$\begingroup\$

I am making turn-based browser game (mostly server-sided), and every turn all game-state is saved into database (SQLite). Using ECS pattern. Never done this before (I am webdev).

There will be a lot of components, mostly simple but a lot of it.

I am thinking about DB structure right now, probably the most important thing for all development, and I have two versions:

  1. One table, entities are rows, components are columns.
+ only one table, easier to code and to think about
- most of this table will be empty, null, 99%
- there can be a lot of small components, like some small tags for very few specific entities, table can become *very* wide (and very empty)
- can't do one-to-many (several same components for entity)
  1. Every component is separate table with entity id per row
+ no nulls, everything is tight and beautiful
+ one-to-many
+ performance
- harder to code
- can have a lot of small tables, like several rows total for some specific entities
- only 1 (tag) or 2 (value) columns for every table, seems like denormalization or vertical partition

What is the generally best option? Or maybe something else.

All info I can find is about in-memory storage (arrays), but nothing about dbs.

\$\endgroup\$
3
  • \$\begingroup\$ Why do you need to store the whole state each turn? Won't it be enough to just store the difference to the last turn or the action performed? Recreating the state would be just by applying all actions that you stored. This could be a feature as well for a light weight reply mechanic \$\endgroup\$
    – Zibelas
    Jan 12, 2022 at 11:24
  • \$\begingroup\$ Mostly because every turn is the last turn, game is not persistent in the memory. \$\endgroup\$
    – Qiao
    Jan 12, 2022 at 12:17
  • \$\begingroup\$ 100% option 2. Having smaller tables means faster lookups. If you have a bunch of nulls you're just wasting energy. \$\endgroup\$
    – Delaney
    Jun 9, 2022 at 3:26

1 Answer 1

2
\$\begingroup\$

I would lean towards option 2.

A system in ECS uses a database query using joins across tables. For example, you might have a system that needs all entities that have a health component and a potion component. When you represent components as tables, this system would be a database join of the health table and the potion table.

However, you're right that option 1 is easier in many ways. I think a lot of games using ECS may be better off with something more like option 1 (e.g. structs with option<> fields instead of separate arrays for each component).

I think you have to decide why are you using the ECS pattern. Once you know the reason you've chosen to use it, you'll be able to evaluate option 1 and 2 relative to your goals.

\$\endgroup\$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .