User defined models and data, how to search/filter/join?

Questions / Help
1

https://www.contentful.com/features/ and https://kenticocloud.com/content-management are headless cms’s that allow users to define data types and fields. https://airtable.com also has user-defined fields and data that allow for filtering/joining.

To model that with postgres/ecto, where a user could search, filter on, or join their data, I came up with a rough idea. Would love any thoughts on this because I’m worried I’m not sure what I’m doing when in comes to indexing and joining on user-generated content.

Table: “Model”
Fields:
ID: (Id)
Type: (String) ie.“Blog Post”
Template: (JSONB) ie. {
Title: String,
Body: String,
PublishedAt: Date,
}
Joinables: (JSONB: { template_type: joinable_field }) ie. { comments: id, author: id }

Table: “Data”
Fields: (For blog post model)
id: (id)
Model_id: (id)
Data: (JSONB [title, body, published_at]) ie. [“A post title”, “a great post body”, “2017-01-01”] <- Is this order reliable?
Joinables: (JSONB) id. {comments: [243, 23, 1], author: 4}

And say a row in the Data table for a comment looked like:
id: 2
Model_id: 2
Data: [“An example comment”, “2017-01-01”]
Joinables: {blog_post: 1} <- Or should it be {model_name: “blog_post”, field_value: [1]} ?

Would this work do you think?

2

A second option to model user-defined content:

Table:
“Model”
Fields:
Type: String (ex. “Blog Post”)
Field1: String, (ex. “Title”)
Field2: String, (ex. “Post Body”)
Field3: String, (ex. “PublishedAt”)
… -> Field19: String (ex. null)

Table:
“Data”
Fields:
ModelId: id (ex. 1)
Field1: String (Ex. "a great blog post)
Field2: String (Ex. “a post body”)
Field3: Date (Ex. “2014-12-10”)
Field4… Field 19: String (Ex. null)

…Then put an index on, maybe like, 7 of the 20 columns? Then say users can only filter/sort/join by those 7 columns? (or maybe all 20, but thinking that may get expensive)

I was hoping to stitch the joins together with Absinthe (graphql)

Do you think this is workable?

3

It’s quite join heavy, but I really like the database schema of processwire for flexible relational datastorage like in a cms.

There are pages (the basic datacontainer), which are hierarchically stored in a table. Those pages have templates and templates have a many_to_many relationship to fields. Now each field is responsible for it’s own table in the database, which can be as simple as pages_id | text, but as complex as a field does need to store information.

4

Thanks a lot! That’s an interesting idea.

It seems that you can cast a field type like string into another field like date or int so sorting is still possible.

I’m going to play around with your idea. Thanks again