Sign In

Tips & Best Practices

This document highlights what we believe are "Best Practices" when developing applications with AppSynergy.

Primary Keys

We strongly suggest using AutoKey for your Primary Keys. We do NOT suggest that you embed any meaning in the key itself (say an "order number" with the date as part of it) as these keys are difficult to generate and maintain.

The most common exceptions to the "always use an AutoKey" rule of thumb are:

We suggest that you NEVER use Composite Primary Keys (these are Primary Keys composed of multiple columns). If you are an advanced database designer, we suggest you instead use an AutoKey for the Primary Key and then add an additional UNIQUE INDEX over the other column(s) that are required to be unique. You can do so easily via Tools > SQL Console... with a command like the following:

CREATE UNIQUE INDEX index_name ON table_name (col_name_1, col_name_2)

Generating AutoKey Values

AppSynergy automatically generates AutoKey values when records are added via the UI. However, if you need to insert records via a database trigger or stored routine you will need to call the parasql_next_val() function to generate the primary key:

INSERT INTO Customer (Customer_ID, Name) VALUES (parasql_next_val('Customer'), 'Bill Smith');

NOTE: You cannot simply leave an AutoKey column blank and have the system generate it (as you would with an AUTO_INCREMENT column). Unlike an AUTO_INCREMENT column, an AutoKey column allows "reservations" of values for use within a modern web UI.

Auto Publish

Once your application is in use by other people, we strongly recommend turning Auto Publish OFF. See File > App Settings... for details.

Be aware that if you modify a database table that an application uses, an AutoPublish will be forced automatically on that application.

Manual Save

Once your application reaches a certain size, the default automatic save behavior can become too slow. In this case we recommend turning Auto Save off by unchecking the File > App Settings... > Auto Save option.

UI Design

Below is a list of what we consider Best Practices for developing easy to use, visually attractive applications.

Modifying Tables

For a variety of reasons listed below, we recommend that you do not modify tables that are in use by a production application except to add a new column to the table or add an index to an existing column.

Any modification to a production database table should be carefully considered:

NOTE: Do not make excess copies of your application(s). Each extra copy of an application must be opened and modified every time a table in your database is modified. Extra copies of your application will slow down the table editing process, potentially enough to cause it to timeout.

Naming Conventions

Consistent naming patterns make databases much easier to work with – especially as they become larger and more complex.

Table and Column Names

We suggest table names and column names be specified in camel case with underscores between words (e.g. Customers, Warehouses, Items, Warehouse_Items, Purchase_Orders, Purchase_Order_Lines). AppSynergy will automatically convert underscores to spaces when building the UI for you apps.

When naming the Primary Key column we suggest you use a name like Customer_ID (rather than simply ID). The reason is that you can often reuse the exact same name as a Foreign Key column name (e.g. Customer_ID) in other tables that reference it; this helps in making things more obvious.

We do not suggest that you use _tbl as part of your table names nor _view as part of your view names because a) these will sometimes appear in the user interface and in error messages etc. and b) the AppSynergy UI distinguishes VIEWs from TABLEs by color on the palette and elsewhere (base tables are black, VIEWs are blue, Federated tables are orange).

Stored Routine Names

When naming stored procedures and stored functions it is often desirable to use a naming convention like this: TableName_MyAction.

For a stored function that will return a value the action part of the name should start with the word "Get" to signify that something will be returned (e.g. PurchaseOrder_GetHtml(po_id) would return the HTML for a given PO).

For a stored procedure that does not have a return value the action part of the name should describe what it does (e.g. PurchaseOrder_EmailToSupplier(po_id) would email a given PO to the supplier).

Security: Two-Factor Authentication

AppSynergy uses Google Accounts for authentication and SSO; we can also support SAML 2.0 if needed. The Google Account can be a G Suite account, a Gmail account, or a free Google Account that uses your existing business email address (you can create one here). In all cases the Google Account will support two-factor via SMS (least secure), the Google Authenticator app on your smartphone (more secure), or a hardware security key that supports "FIDO U2F" like those from Yubico (most secure). We strongly recommend that AppSynergy users with Administrator privileges use some form of two-factor authentication on their account.

Dedicated Servers

If you have an account with a dedicated database server please refer to the Dedicated Server Notes for additional documentation.