Separate Tables per Content Type

[janus-reith]

Currently, all documents are stored in the document table, regardless of their type.
Wouldn't it have advantages to have separate tables per content type?
I would expect most typical queries to only query for one content type, and therefore would assume this to be faster at a certain amount of documents,
and more clean in general.
I did however not do any research yet how feasible this is with typeorm.

Another step for me to consider would be to use mongodb and unwrap data to toplevel fields, but that was discussed in #380 already and doesn't seem interesting for now, so I'd like to have this issue only for the table per content type part.

[birkir]

You will get all sorts of issues with splitting content types into multiple tables.

• Relationships between documents and other tables, like releases
• It may look cleaner from from the SQL-side, but in reality, it will become more complex on the backend side of things
• Dynamically generated tables and ORM's are messy and often times unstable.
• Benefits from SQL over NoSQL like mongodb is performance, relations, structure and scalability

I actually have built a CMS using the method you described, but very soon realised that it was the wrong way to go.

Just my 2c

[janus-reith]

@birkir Thanks for your feedback!

• Benefits from SQL over NoSQL like mongodb is performance, relations, structure and scalability

Wouldn't Performance and scalability be reasons for the exact opposite?

In general, using a document DB like mongoDB seems like the perfect fit for this type of project, but I really don't consider myself a database expert.

Although you already mentioned in that other thread that jsonb is indexed efficiently, I just can't imagine having one big table with some nested json data being superior to having that actual json data as toplevel records in their respective collections, both in terms of performance and ease of use.
I might need to do some Benchmarks regarding the performance.
Also, what if e.g. I would like to apply unique constraints which are only present for some of these data structures?

One main concern with separating the tables was for me, that when building with my data model from scratch, I would probably have separate tables for different kind of data, instead of having them all crammed into one, which makes the dynamically generated data feel kind of subordinate.

Maybe it should also be considered, what type of data something like prime cms is actually meant for.

I wonder if some hybrid approach would be feasible, where you have one part that stays dynamic at runtime and is put into one collection, and one part which is detached before build, which creates separate collections and could also go through a CI.
E.g. for an API that is used by some external inventory to send product data, I wouldn't rely on the current approach, but might still want to have that nice feature of building/extending my api with simple drag and drop ui.
From there I might want to expand it further in code.

[dzcpy]

@janus-reith IMO MongoDB isn't famous for its performance
https://www.enterprisedb.com/news/new-benchmarks-show-postgres-dominating-mongodb-varied-workloads
https://www.arangodb.com/2018/02/nosql-performance-benchmark-2018-mongodb-postgresql-orientdb-neo4j-arangodb/
But in term of easy of use, NoSQL is sure much more convinient to store structured JSON data.