Sign In

Tips & Best Practices

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

Naming

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

Table Names

We strongly suggest that you always name your tables in the singular and in CamelCase (i.e. Invoice rather than Invoices and InvoiceLine rather than InvoiceLines).

Column Names

We strongly suggest that you always name your columns in CamelCase and sparingly also use the underscore character (i.e. Invoice_ID is recommended whereas InvoiceId is not, however InvoiceDate is recommended whereas Invoice_Date is not).

Trigger Names

We strongly suggest that you always name your triggers beginning with the trigger's table name followed by an underscore and the trigger's timing in all capital letters. (i.e. Invoice_AFTER_UPDATE should be the name for an AFTER UPDATE trigger on the Invoice table).

Stored Procedure Names

When naming a stored procedure or stored function we strongly suggest that you 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. Invoice_GetHtml might return the HTML for a given Invoice). For a stored procedure there is no return value so the action part of the name should describe what it does (e.g. Invoice_EmailToCustomer might email a given Invoice to a Customer).

Primary Keys

We strongly suggest that you use an AutoKey column for the primary key on your table. The most common exception to this "always use an AutoKey" rule is for tables that have naturally occurring guaranteed unique values (e.g. a table of State abbreviations, a table of Users with an EmailAddress column as its primary key, etc.).

We suggest that you NEVER use Composite Primary Keys (these are Primary Keys composed of multiple columns). If you are an advanced database developer, 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.

UI Design

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.

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.

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.