Guidelines for extending databases

Modifying sequences is not supported. Editing the sequences.xml file in the entity repository has no effect. Furthermore, it is recommended not to modify the sequences by using the database tools. In most circumstances it is not necessary and has adverse effects.

Certain aspects of the database cannot be modified. If you try to make these modifications, your data is not harmed, but your attempted changes are not incorporated into the database. The application does not permit modification of the following:
  • Existing columns of tables
  • Primary keys of tables
  • Unique keys of tables
  • Views
When planning extensions to the database, consider the implications of your changes and how they may impact other areas.
Note: If you modify a table and your deployment uses the Sterling™ Business Center component, the view associated with the table must also be modified.

It is recommended that custom or extended columns that are added to the default tables or to custom tables are set to Nullable=true by default and do not have a default value, unless it is the primary key column.

Entity relationship diagrams

To learn more about the database, see the entity relationship diagrams (ERDs) using the <INSTALL_DIRruntime_sandbox>/xapidocs/erd/html/erd.html file. These ERDs provide the following information:
  • Indicate which tables can be extended by adding columns.
  • Indicate which tables can have hang-off relationship.
  • Relationships between tables (to help you understand the relationship between logical entities such as orders, shipments, and payments).
  • Indices details. Each table is indexed by a primary key. Most tables also have a unique index that is constituted of the columns that make the logical unique key. In addition, some tables have alternate indices to support queries.
  • Views that indicate how several tables interact.

Entity database XML files

The standard tables that are shipped with the application database are defined in a set of entity XML files, also known as database definition XML files. Each entity XML file may contain several table definitions. To learn more about these tables, see the files in the <INSTALL_DIRruntime_sandbox>/repository/entity directory. Within these entity XML files, an entity represents a table and an attribute represents a column.