Database Design Tips for zeroCode
May we suggest some Do's and Don'ts for good database design?
 
  zeroCode does not require that you follow any of these rules, other than that primary keys cannot be of type DATE and that zeroCode does not support compound keys (keys made up of more than one column). Regardless, following these rules will make your applications run better and be more extensible.
Do's
Use standard Java* conventions while naming tables, columns and other entities.
*All names that begin with and contain lower-case characters, the first character to be in Upper-case. Like in zeroCode.
Use regular English words, that are self-descriptive and without abbreviations while naming tables, columns and other entities.
Create tables with keys that follow these rules:
Every table must have a primary key.
This primary key must consist of only one column. It should be the very first column of the table, can be an integer or a VARCHAR.
Foreign keys must refer only to primary keys and not candidate keys. A foreign keys can be an integer or a VARCHAR.
Table names must not be pluralized.
e.g. A Motor parts table should be named motorPart and not motorParts; the customer table should be named. Customer and not Customers.
You can have a designated candidate key (i.e., unique column) in table A, and you can have a foreign key in table B that depends on A's candidate key rather than A's primary key. We do not assume that a foreign key necessarily points to the primary key, but zeroCode does not directly support auto-UDMs and dereferences via these sorts of foreign keys.
The system does support linking tables that have non-integer primary keys, although they are subject to other constraints. Specifically, since no primary key can be a foreign key, the linking table must have a separate primary key column.
Watch out for CHAR type primary keys. The full character string must be input, including trailing spaces. This also applies to candidate and foreign keys.
Watch out for foreign keys that are not primary keys of their tables. That will not work in a drill-down situation.
Words in table names and column names must be separated by underscores or capitalization (of the first character of the word).
Sequences are identified at site-creation time and built into the objects that each sequence ties to. This cannot be changed at a later date, for an existing site, from the zDE interface. This can be changed by directly editing the sitePrefs.xml file in the deployed site.
Introduce constraints on other columns in each table to ensure uniqueness of user-understood attributes.
For example, on a customer master table, since the primary key is an id, you will need the customer name (for example) to be unique. Such constraints can be compound constraints.
Use VARCHAR instead of CHAR, so that the string is stored exactly as you type it in.
Attempt normalization as far as possible. Normalization makes significant sense in program design and even more so in automated code-generation mechanisms. For example, zeroCode automatically generates references to de-referenced columns on all related pages, based on foreign key relationships.
Tables are repositories of related information and must be treated as such. Where multiple tables "look" the same, i.e. have nearly identical attributes but are meant for different business needs (address information for vendors and customers, for example), separate them into different tables based on their business usage.
Don'ts
Do not use column types and reserved words in naming columns, tables and other entities.
For example, using an 'n' at the beginning of a column name to denote a column of numeric type is a no-no.
Ensure that there are no fields of the type Long.
Do not abbreviate words. Make the names of columns, particularly, easily understood, so that a developer can use them on screen directly. zeroCode, generates field labels from the names directly, after replacing underscores with spaces and inserting spaces before capitalized characters.
Joins must not occur across non-related columns. If you need a join of the form table1.column1 = table2.column2, create a foreign key relationship between table1.column1 and table2.column2.
Do not use Char(1), unless unavoidable - use BIT instead. CHAR(1) in effect means that the meaning of the character in the database (be it '0'/'1' or 'Y'/'N' or whatever) must be interpreted by the application code. In general, to the greatest possible extent, do not assume or imply data interpretation by the application code. Where such a field is required to depict a Yes/No state, use BIT or Boolean. And where more than two states are possible, use a reference table.
Stay away from generalizing your database design to the point of using “intelligent” structures that need typification. For example, if you are not sure about the attributes of a product in your design of a sales order database, do not define a table structure that includes a product header and a table of product attributes, where the name of the attribute and its type would come from a column in that second table. This is not bad database design by zeroCode standards – this is just bad database design.
Constraints:
The system does not support date-type primary keys yet. The SQL processor shows an error if you have that kind of primary keys in your database design.
Every table must have a primary key. The SQL processors shows an error if not.
No primary key is allowed to be a foreign key. The SQL processor shows an error if one is found.
Some tables need an integer key. Examples are:
The login table, if you use self-registration.
The URI list table, if you want to use the load function from the zDE.
All linking tables that will be used in a Relator.
zeroCode supports composite keys. However, when a site is created, the View and Edit screens do not get generated automatically for such tables. These pages need to be customized as per the requirement. You can establish a unique constraint on the combination of composite columns and define an additional non-composite key for zeroCode to work with.
There can be no cyclic references in the database design. Cyclic references are database tables that are related via foreign keys to one another directly, such that the creation of a record in one must be preceded by the creation of a record in the other and vice versa. This can exist by one of the two foreign keys being nullable. Regardless, the SQL processor barfs if such a relationship is found.
There cannot be more than one path between the login table and the URI list table, if page-based security it to be implemented. The SQL processor shows an error if more than one is found.