1

I am building an application in which user can define some entities and its characteristics (attributes / fields), as well as relations. Then, he can populate the entities with data.

For example, user can define the following:

    • Entity: Organisation
    • Attributes: Id, Name, Location etc
    • Entity: Vehicle
    • Attributes: Id, Make, Type etc
    • BelongsTo: Organisation

If the above schema was fixed, we could easily implement it in a relational database. However, the schema is dynamically created by the admin user (the good news is that it will not be that dynamic, it will be set and only changed if needed).

I am considering the following 2 alternatives:

  • Having the tables and their fields being created dynamically. For example, when user inserts Organisation entity, the entity_organisation (and its fields) table will be created. The same procedure for every entity the user inserts.

  • Having the following tables:

    entities (id, name, ...), fields (id, name, type, entity_id), values (id, value, field_id)

    When the user defines a new entity, this will be inserted in entities table and its fields, accordingly in fields table. When the entity is being populated with data, the values will be stored in values table.

I am pretty sure that creating tables dynamically by user input is not a good idea (even if it is by the admin). However, the second approach comes with some problems: 1) many joins, 2) the value column of values table can be any type, according to type column of fields table. Therefore, in this approach I may have to create all possible columns (value_int, value_text, value_boolean etc).

Any ideas? What would be the best approach?

P.S. I want to stick with Postgres (noSQL is not an option)

3
  • 1
    1. Don't do EAV 2. Some SO questions/answers that may be relevant: stackoverflow.com/questions/4304217/… (model diagram may be particularly insightful) stackoverflow.com/questions/4011956/…
    – user212533
    Jul 22, 2021 at 14:57
  • I would also add: having the specific entities and relations fixed (with well defined keys) with certain attributes optional/varying is a far easier approach. Alternatively, define common "entities" as traditional DB tables, have a 6NF or other approach for custom or user-defined "entities."
    – user212533
    Jul 22, 2021 at 15:01
  • 1
    One possibility is a sort of "hybrid" design: have common attributes defined as regular columns, and have anything variable/dynamic defined as a JSON type. Jul 23, 2021 at 8:32

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.