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).

Primary Key Columns: Should follow the pattern TableName_ID (e.g. Invoice_ID, PurchaseOrder_ID).
Foreign Key Columns: Should have the same name as the Primary Key column they link to. In advanced schemas where there are multiple foreign key columns that reference the same table, use a naming scheme like this: From_Warehouse_ID, To_Warehouse_ID (where Warehouse_ID is the primary key column on the Warehouse table).

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

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.
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.
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.

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.

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.

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.

Temporarily Enabling the General Log

If you need to see a log of all database activity you can do so by enabling the general log. Note this logs everything and can therefore get very big very quickly if left enabled. Enabling the general log requires root access, so you will need to enable it and query the log via a third-party tool like MySQL Workbench. The commands are simple:

  SET GLOBAL general_log=1;
  SELECT * FROM mysql.general_log;

After performing your diagnosis we strongly recommend that you turn off the general log via SET GLOBAL general_log=0;