POEM makes some assumptions about database design best practice.
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.
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)
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.
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.
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.
POEM has its own mechanism for ensuring referential integrity. The standard referential integrity policies correspond to those found in most modern DBMS.
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.