for may years we (Agile Dynamics) have been using the SSADM systems analysis & design methodology to design complex databases and workflows.
down the years we have used the following notation or ‘naming convention’ to quickly define the data-types and table references in our data models.
now that i am building data models in Coda, i still find the naming conventions make the task much faster. and, collaboration among several makers is greatly facilitated by this notation.
the steps involved are;
- list the tables that will be needed, giving them suitable names that everyone can understand
- create a ‘template’ table that has a set of button columns for the user interface; eg: an Edit!, Exit!, Delete! and 'Add Child! button with the template formulas defined. this template table is copied to make all the other tables, but has the buttons already set up (which saves a LOT of time; you just need to tweak the formulas).
- create the tables (copy template) and add the columns; the column-names have a ‘suffix’ to show the data-type required. initially they are all text columns (we will change that later)
- starting at the lowest level tables, link them to their ‘parent’ tables. as you go up the hierarchy, you will be able to connect tables to their ‘child’ tables using the ‘Referenced By …’ back-link facility.
- much later, as you create the user interface with pages, views and dialogs; you change the data-type of the columns to match the suffix in the column name
Later you can remove these suffixes if you prefer - they are used mostly during initial development.
The naming conventions we use are as follows
- _CUSTOMERS - table names are upper-case, plural, and begin with underscore
- PROJECT - the display-column for a table is written in upper-case and the singular
- Description - text columns are in proper-case and have no suffix
- Price$ - currency columns end with dollar
- Quantity# - number columns end with hash (includes sliders and scales)
- Progress% - percentage columns end with percent (including progress bars)
- Notes_ - canvas columns end with underscore
- Delete! - button and action columns end with an exclamation
- Repeated? - boolean checkboxes end with question-mark
- Region; - a single-select or single link to a table ends with semicolon
- Tasks: - a multi-select or one-to-many link ends with colon
- Total Amount$` - computed (ie formula) columns end with a back-tick
the _TABLES are never shown to users but kept in hidden pages for developer-use only.
pages, views and dialog-layouts are given Proper-Case names without and prefixes or suffixes.
there is no specific suffix for dates because the column name usually states it is a date (eg. Order Date etc)
canvas controls and named-formulas are given names that start with a lower-case letter.
by using this naming convention, one can rattle-out a set of tables for a complex data model very quickly, deferring the setting of the column-types to a later pass. and during the development phase, it is immediately obvious what each column is used for.
when entering formulas, typing an underscore brings up the list of tables (which is useful as the table names are also frequently used in column-names as well).
column data-types such as email, image, URL and so forth do not have any suffix in this scheme, but they dont occur very often and their type is often implied by the name anyway.
in software development forumns, nothing causes more fiercely-fought holy-wars than discussions of naming conventions - remain calm - i offer these as suggestions only!