Recommendations regarding the database and overall data structure - monarc-common.sql

[adulau]

Reviewing the current initial SQL dump (monarc-common.sql) and the overall structure, here is a series of recommendations (from short-term to long-term):

• The initial database file should be separated in two parts: the schema creation from the data itself (two files should be create like monarc-common.sql - monarc-data.sql)
• Consistency of description fields (what are the default constraints? NOT NULL or empty?)
• What is the default type for description? text? longtext or varchar(255) this can have an impact on indexing
• The current SQL dump is basically a raw dump and needs to be clean-up (initial AUTO_INCREMENT= values should not be there)
• Fixing the field name international English
• Having a single repository for the database (what's the differences between all the various dumps like https://github.com/CASES-LU/MonarcAppBO/tree/master/db-bootstrap versus MonarcAppFO) to simplify the management
• Simplify the data model - remove the 1,2,3,4 stuff and make a proper translation table
• Use meaningful names (e.g. confidentiality, integrity and availability instead of single characters)
• Rethinking the indexes (based on what is really searches from the application) especially that the current model only relies on foreign keys
• Separate template/docx document from the database and put in a separate directory/file
• Create JSON files instead of SQL files for the initial population (to allow external contribution)
• Rethinking the table structure and make it simpler

[cedricbonhomme]

About the JSON files, I also like this idea (for the assets library). In fact, I just found and tested this:
https://github.com/simonarnell/GDPRDPIAT
Interesting. Requirements for the GDPR are encoded in a JSON and used to generated a simple web form. Nice.