Best Practices

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

Naming Tables and Columns

Consistent naming patterns make databases much easier to work with – especially as they become larger and more complex. We suggest table names be specified in singular camel case with underscores used between words:

Recommended table names: Customer, Warehouse, Item, Warehouse_Item
NOT Recommended: Customers (plural), Warehouses (plural), Items (plural), WarehouseItem (no underscore)

Experience has shown the recommended practice is preferred because singular table names make naming Primary Key and Foreign Key columns more natural and consistent. Further, AppSynergy's UI layer will automatically remove the underscore character from labels when building the UI resulting in a better user experience.

When naming the Primary Key column we suggest you use a name like Customer_ID and NOT simply ID. The reason is that you can then often reuse the exact name Customer_ID as a Foreign Key (i.e. link) in another table and it is immediately obvious what it is referring to. In addition you can use abbreviated syntax like SELECT * FROM Order JOIN Customer USING (Customer_ID) in your business logic rather than using the more verbose join syntax SELECT * FROM Order JOIN Customer ON (Order.Customer_ID = Customer.ID).

Recommended Primary Key column names: Customer_ID, Warehouse_ID, Item_ID, Warehouse_Item_ID
NOT Recommended: ID, Id, Customers_ID (plural), WarehouseItem_ID (missing underscore, does not match table name)

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

Naming Stored Routines

When naming stored routines (i.e. stored procedures or stored functions) it is often a good idea to use an object name (typically a table name) followed by an underscore and the action being performed.

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. For example Purchase_Order_GetHtml(po_id) would return the HTML markup for a given po_id. You may also chose to collapse underscores in object names to make the action stand out more from the object, like this: PurchaseOrder_GetHtml(po_id).

For a stored procedure that does not have a return value the action part of the name should describe what it does. For example PurchaseOrder_Send(po_id) would send the specified purchase order (probably sending an email, changing the status to Sent, and logging a Date_Sent on the PO).

Generally speaking scheduled events (aka cron jobs) should follow a naming pattern similar to stored procedures. Also, if the scheduled event is long, it is often good to factor out its key components into a series of stored procedures that are then called from the scheduled event.

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:

CREATE UNIQUE INDEX index_name ON table_name (col_name_1, col_name_2)

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.

User Interface

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

Don't Forget 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. You can create Data Links from within your main application to a modal panel as well as between modal panels. Modal panels are a great way of simplifying the user experience by not presenting too much information all at once. You can create a Modal Panel by dragging its icon off the palette. To edit an existing modal panel see Edit > Modal Panels
Use CSS Themes. Using CSS Themes allows you to easily maintain a more consistent look and feel between screens and applications and provides much more UI flexibility that the quick formatting options available on the tool bar. See File > CSS Themes... for details.
Use Headers. Most Record objects and Report objects benefit from the use of a header. Simply drag a Box object from the palette and give it a dark color background (or use Format > Custom CSS Class... and give it a name like Header that refers to a custom CSS class in your CSS theme to consistently format the look of header boxes). You can then drop objects (like text and buttons) inside this box and set their Text color to white and background and border to Transparent (or better yet use a CSS theme to control what text and buttons should look like by default when used witin a Header box).
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 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.
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.

Generating AutoKey Values

If you are writing a database trigger, stored routine or scheduled event you might run into a situation where you want to create new rows in a table. If the table has an AutoKey column as its Primary Key you will have to generate that value. The built-in function parasql_next_val() does exactly this.

Example of using the function within an INSERT statement:

		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.

Multi-Factor Authentication (MFA)

AppSynergy uses Google Accounts for authentication, and soon will support SAML 2.0 which will enable Single-Sign-On (SSO) with Microsoft Active Directory and many other identity providers. The Google Account can be a G Suite account, a Gmail account, or a Google Account that uses your existing business email address. In all cases the Google Account will support MFA. We find that the Google Authenticator app installed on your smartphone is typically the right level of security for most users. If you need even more security for accounts with access to highly sensitive information, consider using a hardware security key that supports "FIDO U2F" like these from Yubico.

We strongly recommend that all AppSynergy users with Administrator privileges use some form of MFA on their account.

Barcodes & Scanners

Although AppSynergy can work with various types of barcode scanners, some are better than others. External Scanners with Bluetooth HID are recommended. These scanners natively interface with all mobile devices and laptops (i.e. no need to install any drivers, keyboards or apps to make them work). We recommend the SocketScan or Socket Mobile 800 Series for most use cases.

If you are having barcode labels printed and you do not have specific format requirements dictated by your industry or use case, we recommend using barcodes in Code128 format as it is highly flexible (can encode all 128 ASCII characters), compact and modern. AppSynergy can generate barcodes in a variety of formats (Code128 is the AppSynergy default) and are often useful printed on packing slips or other shipping materials.

Dedicated Server Notes

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

Caching A Federated Table

Accessing a large amount of federated data for analysis can be slow, especially where joins and large tables are involved. Often a better solution is to create a local copy of the data at periodic intervals to create a data warehouse of sorts. Although most of our Smart Connectors support automatic caching, sometimes creating an actual copy within the database itself is needed for complex joins or other reasons.

You can accomplish this with a Scheduled Event that runs periodically and refreshes the data (see Tools > Scheduled Events...). The simplest way, if the data isn't too large, is using a commands like the following within a Scheduled Event:

CREATE OR REPLACE TABLE MyLocalTable_NEW AS SELECT * FROM MyFederatedTable; -- this might take time

ALTER TABLE MyLocalTable_NEW ADD PRIMARY KEY (Id), ADD INDEX (SomeOtherColumn); -- this might take time

RENAME TABLE MyLocalTable TO MyLocalTable_OLD, MyLocalTable_NEW TO MyLocalTable; -- this is fast

DROP TABLE IF EXISTS MyLocalTable_OLD; -- this might take time

Dynamic SQL in Stored Procedures

For the very advanced power user, you can use dynamic SQL within a stored procedure using the EXECUTE IMMEDIATE statement. See the EXECUTE IMMEDIATE documentation for details.