Database and Schema Design Guidelines

Important
This document is a WIP. At time of writing, it’s little more than rough thoughts. The document will be more fully developed in the coming weeks.

These guidelines and considerations are a great starting place for understanding the conventions and practices we espouse. As with all guidance like this, however, there are definitely exceptions. Use your best judgement and discuss with DB Architecture to determine the correct path forward.

DBMS Design/Architecture Considerations

• When selecting PostgreSQL as DBMS, use PostgreSQL 11. There are no known reasons to use 10 or below in our use cases.
• …

Naming Conventions

• Identifier casing conventions:
  • In SQL Server and Vertica, use PascalCase naming.
  • In PostgreSQL, use lower_case_underscored naming.
• We don’t typically use tbl_ or vw_/view_ prefixes for tables or views.
  • (Currently unknown if this guidance can apply to all DBMS platforms.)
• Schema names should be short but specific and readable.
• …

Schema Design Guidance

• Tables should try to use a natural key as a primary key where possible, unless the applications has specific performance needs.
• In PostgreSQL, use identity instead of serial as identity is more compliant.
• If you use separate schemas, they should not be joined to each other except through a single specially designed join table.
  • This helps enforce a separation of concerns between schemas and create an authoritative mapping of truth across separate conceptual domains (the schemas).
• Quantities are always a numeric-equivalent type, not an integer type.
• An identity column should never be a primary key. If you must use one, you must either have a unique key elsewhere or declare in design that the table has no requirements for uniqueness (e.g. a log table).
• …

Database Code Conventions

• Never use SELECT * or equivalent in queries that are a part of application logic.
  • Using it in “ad-hoc” investigative queries or during debugging is fine, but it should never appear in production queries.
• …