Database fields
From Dokeos
First of all let us refer to Database refactoring article where everything is centralised
Many database table and field names are still the same as the older Claroline software. These names are often not real english but franglais (meaning a sort of frenchified english).
Refactoring and improving database design - guidelines
- all database fields should be in English
- all database fields should use names that are not on the (My)SQL reserved keywords list (see http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html)
- to enable future portability, special MySQL quotes (`) should NOT be used. This also allows the detection of fields that use reserved keywords (see previous point)
- each table should have a comment to describe what it's used for
- should we delete obsolete field?
- consistency in the field types. For instance: COURS.link.category int(4) <-> COURS.link_categories.id int(6), cours_user.user_id int(11) < > user.user_id meidumint(8), faculte.id int(11) < > course.faculte varchar(12)
- some tables should be deleted
- every single change must be written down in a script that is easy to integrate into upgrade scripts.
- enum-type fields that only hold numeric values should be avoided (as described at the end of this page: http://dev.mysql.com/doc/refman/5.0/en/enum.html). tinyint fields should be preferred
The database documentation on the Dokeos website might be a great source of inspiration for this refactoring.
Decisions
- Naming convention for the table and field names: we always use under_score, not camelCase.
- For consistency database fields from different tables that contain the same entity will all be given the same size / length. We agree to enlarging the fields to get the same length, and never reduce fields in size.
