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:

• Simple look-up tables: For example, you might have a list of U.S. states where a two character column containing the state abbreviation is the logical Primary Key for the table.
• Limited number of records with natural short descriptors: If the list of possible values is short (say in the case of a table of warehouse locations that your company operates) it would be better to have the Warehouse_ID as a 20 character column rather than AutoKey because then you could label your warehouses with a short alpha code that is immediately obvious to users (e.g. Raleigh, Dallas, etc.).

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:

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:

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.

• When Auto Publish is OFF, other users cannot see the changes you make to your application until you click File > Publish App.
• With Auto Publish OFF you also have a longer Revert To history to undo unwanted changes. The last 10 published versions of your application are kept, along with recent unpublished changes, which makes undoing mistakes easier.

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.

• Use Modal Panels. A modal panel is a pop-up panel that forces the user to interact with it before they can go back to using the rest of the application. Modal panels are a great way of simplifying the user experience by not presenting too much information all at once. See the Modal Panels Tutorial Video for more info.
• Use Header Boxes. Most Record and Report objects benefit from the use of a header. Simply drag a Box object from the palette and then drop objects (like text and buttons) inside this box. You will see this being done in various tutorial videos.
• Delete Buttons. If you decide a Record needs a Delete button, place the Delete button inside the record – not outside the record – so there is no ambiguity of what is being deleted. Also, consider not having a Delete button at all, as it is often better to design applications with something like an Inactive checkbox on the record and then use filtering to control what is shown to the user.

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:

• Adding a new column to the end of an existing table is safe.
• Changing the Index attribute of a column is safe.
• Changing the Required attribute of a column is safe. If the column has blank values, you will need to specify a value for the blank rows via Tools > Bulk Update... before making the column required.
• Changing a table name or column name MIGHT cause problems. When a table name or column name is changed, AppSynergy modifies every application that references that table name or column name. However references to that table or column name in formulas, filters, triggers, stored routines, and scheduled events are not updated; you will need to fix these references manually.
• Changing a Primary Key or Foreign Key column will LIKELY cause problems. AppSynergy requires that you break any foreign key links, save those changes, rename, then recreate the foreign key(s). Doing this will cause foreign key column formula references to break; you will need to fix these references manually.
• Changing the datatype of a column will LIKELY cause problems. Most datatype changes require that AppSynergy remove any Field objects based on that column from your application(s); you will need to add the Field object(s) back into your application(s) manually. The exceptions to this are 1) lengthening a character column or 2) switching between numeric datatypes; both should be safe unless you have written SQL code that expects a certain length or datatype.
• Timeouts can cause problems. If you make a change to a table that requires AppSynergy to modify a reference in an application, and the table contains enough data that it cannot complete such change in less than 60 seconds, a timeout will occur. In these cases the table might be modified but the application(s) references are not. We suggest that you do not modify schema in such a way that it requires AppSynergy to modify application references once your application is deployed into production.

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.