Skip to content

Database Design

Consider the database design guidelines below.

 

Use Foreign Keys

These are required for multi-table rules (sum, count, copy etc), and for multi-table pages.

If these are not in your database, you can add them in to the created data model class. For example, find Employee.Manager in customize_models, here.

 

Foreign Key Indices

In general, add indices for your Foreign Keys. Note performance may be fine in dev, but degrade when product data volumes are encountered (e.g., pre-production testing).

 

Initialize Stored Derivations

Recall that the logic engine uses adjustments to maintain aggregates, instead of expensive (possibly nested) aggregate queries. This can result in order-of-magnitude performance advantage.

It does, however, rely currently stored values being accurate. Logic will ensure remains true, but you must ensure it's initially true. Introducing stored aggregates in an existing database with existing rows requires you initialize new sums and counts.

For example, let's assume you introduced the Customer.Balance as a new column in the northwind sample database. You'd need to initialize the Balance like this:

update Customer set Balance = (select AmountTotal from "Order" where Customer.Id = CustomerId and ShippedDate is null);

# Then, verify with:
select CompanyName, Balance from Customer where Id="ALFKI";