PRODUCT
SOLUTIONS
RESOURCES
This document highlights what we believe are "Best Practices" when developing applications with AppSynergy.
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:
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).
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).
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.
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:
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.
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.
Below is a list of what we consider Best Practices for developing easy to use, visually attractive applications.
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:
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.
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:
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.
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.
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.
If you have an account with a dedicated database server please refer to the Dedicated Server Notes for additional documentation.
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:
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.