I do a lot of php development in a cms/cmf called processwire for a living, while curiously looking into elixir/phoenix. ProcessWire’s entity modeling is based on a modeling strategy, which I found to be more flexible, than only the plain has_one/has_many/… relationships most frameworks seem to use/suggest.

Before explaining it in more details: My question is, if such a structure would be possible to use with ecto/phoenix and what you think might be the downsides/dealbreakers for it.

So modeling in processwire is not based on a single table per model, but models are rather a core set of infos about the model entity (id, name, template, created, modified) and a set of custom fields associated to the model (template). Each of the custom fields does have it’s own table, which in simple cases consists only of entity_id and data columns, but it certainly can hold more columns if needed by the type of field like sort for multi-value fields.

So a template for a blogpost, which does hold the custom fields of “title”, “tags” and “body”, would result in the following tables:

templates (has the info about which fields the blog post is associated with)
entities (base info like above)
field_title (one-to-one mapping of title to post)
field_tags (one-to-many mapping of tags to post)
field_body (one-to-one mapping of body to post)

Via the template information on each entity the data of the custom fields can be joined into the base information or being lazy loaded after the retrieval of just the core information about the entity.

The advantages of such a mapping to me are the following:

• All entities ids are stored in a single table, which makes linking of entities of any kind super simple.
• All entities can have has_one or has_many relationships to their fields, so multi-value fields are simple to do as well

Both together allow for structures, where an entity can link to a sorted list of differently typed entities without additional pivot tables or such things. For example in a portfolio website a project entity’s field aside_info could return a list like this [%Image{}, %VimeoEmbed{}, %Image{}, %Image{}].

A second feature of this modeling strategy is that data can easily be stored in a tree structure. By adding a parent_id column to the entities table everything can live in a global tree of all the entities in the system.

With every model entity being of the same base, one also does rather care about how to store data relevant to a specific type of field instead of creating a title (varchar(255)) on each model again. One does rather compose exisiting fields with fields unique to a specific model.

This might not be a perfect layout of what happing in processwire, but I tried to keep the explanation reasonably short. I curious to hear your thoughs about it.