MYSQL - best Data Structure

Question

I’m currently developing an Application for Win, Linux Mac. The Purpose of the Application is that multiple users are able create Projects based on a single Article. Every Article has up to 15 different Fields/Options (could also be more in future). The Fields of the Article should be changeable so I should be able to add, edit or remove them.

Fields I want to store:

• Numbers
• Texts (mostly options [1 Word], sometimes Comments [some sentences])
• Path/Links to Files

What I want to do with the dB:

• load all projects of a user at login

• add, edit, remove, delete single projects

• set a lock on projects (because multiple people are operating one user-account at the same time and therefore they may not be allowed to edit a project at the same time so if one starts editing it should be locked until he's saving, channelling or time-out)

What is the best way to manage this kind of Data? Should I create a Table for each user and only make a ID Column and one where all the Values of the all the fields (who are merged to one big string)? Should I create Tables for every Project and make Columns for every Field/Option and also one for the user / owner? Or are there any other possibility’s?

Answer 1

If you don't know what you are going to store, then I doubt whether a relational database is the best option for you. Maybe a document store/noSQL database is a better decision, because you can just store documents (usually in the form of Json objects) that can have all kinds of additional fields.

A couple of such databases to look at are MongoDB, Cassandra, ElasticSearch, but you can find a big list on Wikipedia.