Fork me on GitHub

Database Design

POEM makes some assumptions about database design best practice.

Table and Field Naming

Ideally database table names are all lowercase, the java class name will be all lowercase with first letter capitalised.

Whatever case you use in the DSD will be respected and reflected in the java class names, method names and database table names created.

Names cannot start with a number.

Keys

All tables are expected to have a unique field calledid, it is this field that is used when this record is referred to in another record.

Keys are dataless, unchanging, unique and unambiguous (DU3)

Indexing

All fields designated as searchable have an index created on them if they do not already have one. Melati assumes that the dbms will plan queries effectively using whatever keys it has available.

The database application balance

The balance of responsibility between an application and its datastore is a key consideration in the architecture of a system.

POEM is designed to enable rapid prototyping and to be database agnostic. This means that POEM attempts to make as little use as possible of the underlying database management system (DBMS). The DBMS syntax required is split out into the dbms package where each dbms idiosyncrasy is made explicit. This makes switching applications from one dbms to another trivial and often effortless.

The project however may require more extensive use of the underlying DBMS, perhaps the care and maintenance of the database is to be handed over to a dedicated DBA or there may be a a desire to use a schema graphing tools (such as Schema Spy). To enable this there is an optional property on a Melati LogicalDatabase to add constraints. When set primary key constraints, foreign key constraints and referential integrity constraints are added to the database.

Constraints are added after the database has been initialised, to avoid the problem of referring to a table in a foreign key before it has been created.

Note that there may be a downside to adding constraints. It may mean that you need to manually delete these constraints before being able to alter your table structure.

Primary Key Constraints

POEM only ensures that primary keys are indexed and unique and creates such an index if it is missing.

When the AddConstraints property is set then Primary Key constraints are added to the Table Row Id (TROID) column of all tables.

Foreign Key Constraints

POEM does not use the DBMS' Foreign Keys, as it maintains its own metadata in the ColumnInfo table, but for schema export or reporting this metadata needs to be known by the DBMS.

The AddConstraints property allows the setting of these constraints.

Foreign Keys should not have any impact upon a Melati system, though there is a chance of increased or decreased performance depending upon your DBMS.

Referential Integrity Constraints

POEM has its own mechanism for ensuring referential integrity. The standard referential integrity policies correspond to those found in most modern DBMS.

POEM SQL
prevent RESTRICT
delete CASCADE
clear SET NULL

Of course if you have written your own POEM IntegrityFix fix then this will not be communicated to the DBMS.

Note that POEM does not have an equivalent of ON UPDATE as POEM keys are intended to be unchanging.