dynamic schema modeling and performance

[RJ Ewing]

I'm looking for thoughts on the best way to handle dynamic schemas.

The application I am developing revolves around user defined entities. Each entity is a tabular dataset with user defined columns and data types. Entities can also be related to each other through Parent-Child relationships. Some entities will be 100% user driven, while others (such as an entity representing a photo) will be partially user driven (all photo entities will have common fields + custom user additions).

I was hoping to get opinions on whether mysql would be a suitable backend. A couple of options I have thought of are:

1. Each entity is represented as a table in mysql. The table schema would be dynamically updated (with limits) when an entity mapping is updated. I believe that this would provide the best data constraints and allow the best data normalization. A concern I have is that there could be an enormous amount of tables generated and the performance impacts this might have in the future. I could then run elasticsearch as a denormalized cache for efficient querying and full-text-search.

2. Use a nosql database. This provides the "dynamic" schema aspect. A concern here is the lack of relation support, thus leading to a more denormalized data structure and the potential for the data to become corrupted.

Is dynamically creating a table for each entity a bad idea? I can see something like creating a database for each project (group of related entities) and then creating a table for each entity. I don't expect having more than a few thousand projects anytime soon. We have a relatively targeted audience.

Or would it be better to use 1 general table with a json data type and create a denormalized index elsewhere for querying?

Any opinions on the use of mysql for this case, or other options would be greatly appreciated!