SixArm → SQL → Schema examples

Contents:

• Schema examples
• Schema conventions
  • General standardizations
  • Dates and times
  • Our SQL conventions
  • Our field name conventions
  • Languages
  • Types
  • Optimizations
• Bonus fields for growth
  • Secure random primary keys
  • Optimistic locking
  • Change tracking
  • Single table inheritance
  • Position order
  • Parent pointer
  • Status
  • For more information
• Liquibase annotation
  • Why we use Liquibase SQL vs. XML vs. YAML
• Tracking

Schema examples

Most popular:

• person
• place
• thing
• org
• event
• action
• schedule
• tag
• color

Access control:

• access_user
• access_assigment
• access_attribute
• access_permission
• access_operation

Geography:

• geolocation
• country
• country_subdivision
• region
• locality
• street_address
• neighborhood
• postal_code
• postal_address

Business:

• brand
• currency
• currency_conversion_service
• currency_pair
• exchange_rate_specification
• market_area
• market_sector
• money_range
• money_transfer
• money_value
• offer
• payment_service
• product
• trade_action

Accounts:

• bank_account
• ftp_account
• pop_account

Misc:

• rag
• image_filter
• lifespan

Schema conventions

General standardizations

Use industry standard schemas such as the Schema.org project.

• For example, a postal address has a field for "locality", not "city".

Use International System of Units (SI), such as the metric system.

• For example, the field name "height" is intended to use a unit of a meter, not a unit of a foot as in the Imperial system and United States customary system.

Dates and times

Use date formats and time formats that are consistent with ISO standard formats.

• For example, the timestamp display format is Year-Month-Day and Hour:Minute:Second and "Z" i.e. Zulu time zone, such as "YYYY-MM-DDTHH:MM:SSZ"

Any date or time field must be in UTC, unless the filed is deliberately representing local time, in which case the field name must use the suffix "_local" because this helps with disambiguation.

• For example, the "person" table has the field name "birth_date_local" because we care about the person's local date birthday, not Zulu date birthday.

If you are using PostgreSQL, then you may want to change the "timestamp" data type to the PostgreSQL extension "timestamptz" data type.

• For example, the "event" table has the entry "start_when timestamp"; if you are using PostgreSQL, you probably want to change this field to "start_when timestamptz" for Zulu time, or "start_when_local timestamp" for local time.

Our SQL conventions

Use a table name that is singular, not plural.

• For example, the examples have a "person" table, not "persons" nor "people".

Use lowercase SQL, rather than uppercase SQL.

• For example, the examples use "create table", not "CREATE TABLE".

Our field name conventions

If a field can be a relation and/or freeform text, then use two fields, one with field name suffix "_id" and one with field name suffix "_ie" meaning "I.e., in other words".

• For example, a field name "status_id" is intended to be a relation to the "status" table, and a field name "status_ie" is intended to be freeform text that user can enter.

If fields are intended to be a range, then prefer "start" and "stop", not "begin" and "end", nor "from" and "to".

• For example, the table "event" has a start time and stop time, not a begin time and end time.

Use a field name with a long meaningful name, not an abbreviation nor initialism.

• For example, use "latitude", not "lat".

Languages

We prefer to work with multiple languages. For example, we often use English, Spanish, Chinese, and many other languages.

• For example, a person's name in English is "Amy" and in French is "Aimée".

We use a naming convention of "{field}as{language}".

• For example, the table "person" has a field name "name_as_en" meaning "the name as English", and a field name "name_as_fr" meaning the name as French.

For the example files here, we list English, French, and Spanish. This is so you can see how multiple languages can work.

You can add more languages if you want.

Some developers prefer different ways of handling languages, naming, internationalization, and localization. You can customize the files as you like for your goals.

Types

Use typical data type default sizes.

• For example, we use the data type "varchar" as a default, when we don't know a text field will be somewhat short.

Use numeric data type with a large range, rather than float data type, because we prefer consistency over fluidity.

• For example, we prefer numeric(20,12) as a general-purpose number type; you can change these as you like.

Use the word "numeric" instead of "decimal" because its clearer, such as for integrations.

• For example, we prefer numeric(x,y) over decimal(x,y).

Optimizations

Fast speed is more important than space, so we prefer some denormalization.

• For example, some tables duplicate the field name "postal_code", because many of our apps use it to speed up local search.

Handling corner cases well is more important than space.

• For example, the concepts of a "region" and "country_subdivision" are nearly identical, but not quite, so we store both.

Providing usable represenations is more important than space.

• For example, the table "person" and table "organization" both have a field name "vcard" that stores the VCard VCF text, and a field name "hcard" that stores the VCard as a HCard HTML microformat.

Bonus fields for growth

In practice we often add some bonus fields to each table; these fields help us with the growth of the app, and also the administration of the app.

Secure random primary keys

Example:

-- We prefer using secure random 128-bit numbers as primary keys.
-- These numbers are storable in a typical PostgreSQL UUID field.
id uuid not null primary key,

Optimistic locking

Example:

-- An incrementing number that an application can
-- use for optimistic locking for read/write speed
lock_version int,

Change tracking

-- Track who touches the record and when,
-- because this information helps in practice
-- for diagnosing the application as it runs.
created_at timestamp, created_by uuid references user,
updated_at timestamp, updated_by uuid references user,
proofed_at timestamp, proofed_by uuid references user,
retired_at timestamp, retired_by uuid references user,

Single table inheritance

Example:

-- The field name "type" is a reserved word in some frameworks,
-- which uses the field for single-table inheritance.
type varchar,

Position order

Example:

-- The field name "position" is a reserved word in some frameworks,
-- which uses the field for ordering by position index number.
position integer,

Parent pointer

Example:

-- For a record that has a direct parent record
parent_id uuid references self,

Status

Example:

-- Status table suitable for the app
status_id uuid references status,
status_ie varchar,

For more information

Example:

-- Ways to see more about the record, such as a URL to more information, and a note of text.
uri varchar,
note longtext,

Liquibase annotation

Liquibase is an open source tool for managing database schema changes.

See https://en.wikipedia.org/wiki/Liquibase

Our projects use database schema changes within source code, and within large projects, so we have schema examples here to help new projects.

Why we use Liquibase SQL vs. XML vs. YAML

Liquibase files can be written in SQL, or XML, or YAML. We prefer SQL because more database administrators know it.

If you prefer XML or YAML and would like to translate our examples, then we welcome the help and also welcome pull requests.

Tracking

• Package: sixarm_sql_schema_examples
• Version: 8.3.1
• Created: 1996-01-01
• Updated: 2019-05-16
• License: BSD, MIT, GPL
• Contact: Joel Parker Henderson (http://joelparkerhenderson.com)