Table Standards and Best Practices

This page goes over various standards that should be followed when developing database tables. Consider this to a be list of best practices.

Data Modeling

  • If you are unsure whether to add a dedicated lookup table or a "hard-coded" column, then add a dedicated table. It takes very little extra time to make a dedicated lookup table and you could be glad in the future that you did it this way. Hard-coded columns save you a few minutes in the beginning but they often leave you with future problems if the rules behind the columns expand. Example, what if, down the road, we want to show certain information on eWeb based on this data? If you have a hard-coded column, you're stuck. If you made a table, then you can easily add a "show on web" column. See also DropDownList on the Wiki for more on this topic.
  • If you have a lookup table, then on the related table that links to this column, NEVER make the foreign key column link to the lookup table's "code" column, always link to the "primary key" column. This is a more formal and better database design. Remember to add this foreign key reference.

Example:

  1. Table client_xyz_buiness_code has a01_key and a01_code.
  2. Table client_xyz_customer_x_buiness_code has a02_key and a02_a01_key (FK to client_xyz_business_code.a01_key) and a02_cst_key. Do NOT have a02_a01_code! On forms, depict a02_a01_key as a DropDownList (if there are few records in the parent table) or a Lookup. Add a Related Object to make these work. See Related Object for more information.
  • Instead of adding a FK reference to co_organization or co_individual add the FK reference instead to co_customer.cst_key, even if you develop the forms in such a way that only an Individual or an Organization can get data into this table. This way you can set the Lookup to co_customer.cst_sort_name_dn, and it's cleaner to put the reference all the way up to the Customer table. Finally, if you limit this table to just one customer type in the database, you cut off your options in the future if you want to allow more than one customer type. In baseline, there are some exceptions where references go to the Individual or Organization table, but usually the FK reference goes right to co_customer. Search the table names and Lookup for more information.
  • Modeling a database takes experience. If you are unsure, ask someone for suggestions.

Naming Convention

  • Table names are all lowercase.
  • Column names are all lowercase.
  • For custom tables, begin name client_abcde_ where abcde is the client's abbreviation.

See Table/Column Naming Conventions for more information.

Columns

  • Don't forget to make sure the required foreign key columns are NOT NULL. It is a common mistake to have a _x_customer table with a q87_cst_key be NULLABLE.
  • Think about the Data type of a column in the beginning. If you discover later that you made a mistake, this can be an extremely difficult problem to correct.
  • Name the columns carefully. See Table/Column Naming Conventions for more information. Most common mistake:
    • Not putting "flag" in a av_flag column
  • Extender columns must have _ext suffix.

Data Integrity

  • Do not forget to add foreign key constraints. This is frequently forgotten and is inexcusable.
  • Where appropriate add CHECK CONSTRAINTS to validate certain data. Example, before date must be less than or equal to end date
  • Add unique constraints on CODE columns. If this is an entity system, put the constraint on code + entity_key.

Indexes

If you have a table client_abcde_co_customer_x_business_code table that has x41_cst_key and x41_x89_key, you should probably add an INDEX on each of these columns for faster child form retrieval and faster searches and queries.