Improve Database Naming

Owned by Former user (Deleted)
Last updated: Sept 07, 2010Version comment

Context

issue #1513 highlights portability issues with the Mifos database backend and the need for a consistent naming convention for database identifiers.

The MySQL manual describes how to ensure portability to different operating systems: the database and table names should be lowercase. However, the current schema and all the Hibernate mapping (hbm) files contains all names in uppercase.

The current SQL scripts contain mainly uppercase names with some lowercase for table and column names.

Decisions

In the 14th February 2008 Planning Poker meeting, we decided to use only lowercase table names, assuring MySQL database portability between different platforms and enhanced readability and maintainability of Mifos code.

There are different opinions on elements that don't affect portability such as column names and other SQL elements.

Here following some recommendations as starting point to address the concept of Database portability also to other Databases than MySQL. A complete Naming convention cannot address just case sensitivity to fix the current issues on portability of MySQL Mifos database on multiple operating systems. The naming convention should address future development and it shouldn't be just a point fix.

Consistency/Readability

Although database and table names are not case sensitive on some platforms, you should not refer to a given database or table using different cases within the same statement. The following statement would not work because it refers to a table both as my_table and as MY_TABLE:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

We can say that the first rule is having consistent sql statement.

Avoid reserved keywords

This should be done at static check, so a consistency in lower case will simplify a lot this check. We will suggest a list of reserved word also for DB2 and Oracle Database, other than the MySQL ones.

Mandatory and Recommended Naming Conventions

See DatabaseNamingConventions.

Static Consistency Check

A static code analysis check on the SQL scripts will be done using PMD or a custom unit test depending on the complexity and timing. A Java main application might be written to check and eventually enforce the respect of naming conventions defined. Having columns and other objects lower case will help to write the check.

Resources

Missing Foreign Keys

There are places where there are or possibly are missing foreign key definitions

Tables

  • account
    • CREATED_BY (links to personnel)
    • UPDATED_BY (linnks to personnel)
    • VERSION_NO (Not sure what this links to)
        • OFFSETTING_ALLOWABLE (Not sure where this comes from)