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.