Acuity™ and the CEDS Database
Why does Acuity™ use a CEDS-Like Database
One reason we use "CEDS-like" instead of a pure CEDS™ schema is that the official schema contains 728 tables, of which 466 are lookup tables. This level of database normalization is good practice at the federal level where the schema is designed and maintained, but at the SEA level where it is used, 64% of the tables contain read-only information and having them in separate tables only slows down the use of the database and causes locking problems to an unacceptable level.
A second reason we diverged from the CEDS standard was to be able to support Oracle back-end databases. The CEDS schema was developed using Microsoft SQL Server, which allows designers to use as many characters as they desire when choosing names for columns, tables, indexes and foreign keys. Since we wanted to be able to support this product for other back-end databases, we needed to choose a more-portable naming convention.
Our original release supports SQL Server and Oracle 12c, where Oracle has the more restrictive naming conventions. Later releases of Acuity will support MySQL (Enterprise), since it shares naming conventions with Oracle.
When we assessed where there would be naming convention problems (Oracle allows most names to be at most 30 characters long while SQL Server allows 128 characters):
- 192 table names had to be changed (they were too long for any database besides SQL Server),
- 827 column names were too long and
- 1,520 key names (indexes/foreign keys) were too long.
In conclusion, the schema is not portable and, due to basic database restrictions, the CEDS schema as published on the official site cannot be implemented in any database other than SQL Server without thousands of modifications.