Development Guidelines - OpenNMS/horizon-stream Wiki

Postgres Naming Conventions

These are general guidelines for when adding new tables, columns, indexes etc. to the Postgres database.

Many of the existing objects in the database that were ported from Horizon Classic do not follow these conventions - there's a mix of singular and plural words, underscores, no underscores, etc. If you are modifying something that does not follow these conventions, like adding a new column to an existing table, follow the existing conventions of that table.

These conventions attempt to match what Postgres uses by default and are up for debate.

General Rules

These general rules apply to all database objects and match the conventions Postgres uses when generating things.

  • Use singular forms of words.
  • Use lowercase letters.
  • Use underscores between words.
  • Name database objects after their code/ORM representations.
  • Follow existing conventions. Do not add columns with underscores between words if none of the other columns on that table use underscores.

Tables

  • Tables should be named the same as their entities in code.
    • For example, if a Java class for an entity is called MonitoredService, the table for that entity should be called monitored_service.
  • Join tables used by many-to-many relationships should be named {ownerTableName}_{otherTableName}.
    • The first table referenced should be the "owner" of the relationship, which depends on how the relationship is modeled and up for interpretation. Generally, if the code specifies a @ManyToMany annotation in both entities, one will include joinColumns and inverseJoinColumns. That entity is the owner. The other, non-owner entity will specify mappedBy.
    • For example, the join table for a relationship between monitored_service and application should be called monitored_service_application. MonitoredService is the owner of the relationship.

Columns

  • Columns should be named the same as their associated fields in code.
  • If the code for an entity uses a different name for a relationship, prioritize the code's name.
    • For example, if an entity has a relationship called distPoller to a table called monitoring_system, the relationship's name in code takes precedence. Name the column dist_poller_id and the constriant dist_poller_id_fkey.

Indexes

These match the conventions Postgres uses for generated index names.

  • Indexes should generally match {tableName}_{columnName(s)}_idx.
    • For example, monitored_service_status_idx.
  • Indexes for special purposes (like partial indexes or query optimizations) can be named something that makes sense, matching {tableName}_{indexName}_idx.
    • For example, monitored_service_managed_status_idx.

Constraints

Postgres generally follows a naming convention for constraints that looks like {tableName}_{columnName(s)}_{suffix}. These guidelines attempt to follow that convention.

Primary Key Columns

  • Identity columns (primary keys) should use GENERATED BY DEFAULT AS IDENTITY. This will automatically create the column as an identity column, which automatically adds a primary key constraint and a sequence.
  • The primary key of a table should be named {tablename}_pkey. This matches the constraint name Postgres would have generated if you didn't specify a name.
    • For example, the primary key constraint for a table named monitored_service should be called monitored_service_pkey.

Foreign Key Columns

  • Foreign key constraints should be named {tableName}_{columnName}_fkey. This matches the name Postgres would have generated for the constraint if you didn't specify a name.
  • Generally, foreign key columns should be named after the table and columns they are referencing.
    • For example, a foreign key to a table named monitored_service and a column named id should have a column named monitored_service_id and a constraint named monitored_service_id_fkey.
  • If the code for an entity uses a different name for a relationship, prioritize the code's name.
    • For example, if an entity has a relationship called distPoller to a table called monitoring_system, the relationship's name in code takes precedence. Name the column dist_poller_id and the constriant dist_poller_id_fkey.