Database Tables

Parent Previous Next

  1. Use single tense for table names, e.g., “product” not “products”.

Note: this is a hotly debated topic, but the overwhelming consensus is to use single tense for table names. Think in terms of dot notation, "product.type" sounds right, but "products.type" does not sound right.

  1. Do not use generic prefixes in table names such as tbl_, db_, a_, e.g., "tbl_mytablename". Plus other Developers will recognize you as an Microsoft Access developer.
  2. The first field in a table should be the primary key (PK), followed by all of the foreign keys (FK) followed by other fields roughly in order of significance and/or by category.
  3. SQL-only: Logical fields must be tinyint(1) in MySQL, and bit(1) in MSSQL. Check Alpha's documentation for other SQL databases.
  4. Required/SQL-only: Always default logical fields to zero (0) rather than null. Ensure any existing null records are changed to zero.

    Reason: Alpha Five recognizes tinyint(1) and bit(1) as logical fields in grids and Xbasic. Zero (0) is false, one (1) is true but null is only "not true" and this can trip up your code if not handled properly.