General Database Conventions

Here, you will find the general rules and conventions for databases that apply accross database types.

Schema Conventions

Use schemas to group related objects

Using schemas to group objects with similar security requirements

There are two schools of thought on how to use schemas in database. One school, let us call them “the security school” argues that schemas should be used to group objects with similar security requirements together. The other school (which I belong to) argue that schemas should be used as a form of table/stored procedure prefixing, grouping similar objects together.

The security school has the problem that permissions change frequently in database and by following this line of thought – fully qualified table names now have to change too. Some of this can be circumvented with clever use of synonyms and views, though this quickly becomes messy. However, a more important argument against the security school is that there already exists another mechanism in a database for grouping security access pattern together: database roles. By using the schema for security, there are now two ways to achieve the same effect – which is somewhat redudant.

Another issue with the security school is that schemas in database like MySQL are also transactional containers. The boundary of a single, non distributed, transaction is the schema level. If schemas are used to group security roles, queries cross multiple schemas now have to cross transactional boundaries too.

It is worth noting that until Microsoft SQL Server 2005 (version 9.00.x) schemas and database roles were overlapping concepts; you could not create one without creating the other. This historical anomaly is not a good reason to use schemas for security, its just an indicator that SQL Server got it wrong all the way until SQL Server 2000.

Table Conventions

Name tables in singular

Use plural names

Queries that reference plurally named tables seems quite elegant at first glance. For example SELECT ... FROM Persons looks quite natural. However this idea quickly breaks down once you consider the following:

• Not all tables contain multiple entities. Which makes Persons the wrong name for a table with a single person
• When you use plural naming, how should the key then be named? Using the example of Persons will we call the key ID_Person or awkwardly: ID_Persons? If singular is used to name keys, then we get in trouble when we try to auto generate (for example via code) the name of the key. The code will then need to translate between plural and singular naming. Good luck finding a consistent pluralisation engine to use in the code. Consider the plural of person, is it “persons” or “people”?
• When creating relationship tables, usage of plural becomes problematic. Consider a plural naming convention: How do we name the M-N relationship table between: Persons and Addresses? Should we call this table Persons_Addresses? And if we do, what do we call the keys in that table?
• Being inspired by objects orientated programming, we don’t generally use plural names for classes and singular names for instances. If we did, we would awkwardly have to write: Persons person = new Persons(). Instead, this is the natural way to express this: Person person = new Person().

For a detailed discussion about plural vs. singular naming see this Stack Exchange Topic.

Use TitleCase naming

Use another casing and word concatenation schema

There are several alternative ways to name tables and columns that I have rejected. They are listed here

Do not prefix table name with their data type (with the exeption of bit)

Use hungarian notation (example: prefix with tbl)

By prefixing tables with their type, it becomes hard for the DBA to replace tables with view/synonyms to maintain schema compatibility

View Conventions

Name views the same way tables are named

Pre- or postfix views

While it may seem desirable to identify views purely by their name – this is actually counter productive. When reorganising a database, it is often convenient to temporarily replace some tables with views instead and maintain interface compatibility. In other words, it is in the interest of the DBA to prevent consumers of the database from distinguishing between views and tables. While these solutions are often meant to be temporary, they will often stick around for a long time. Because of this, it is not advisable to name views in such a way that they can be identified as views.

Column Conventions

Use singular naming of columns and follow the rules for tables

See the argument for table naming

When a colum represents a collection (breaking of normalisation rules), use plural

This is the sole example of when plural naming makes sense. For example, consider the case of a Person table with multiple salutations stores in a comma seperated varchar. While this is normally bad practise, doing this may make sense from a performance perspective. In this example, the list of salutations should be named Salutations.

Name bit columns with the prefix Is in such a way that it is clear what the bit expresses.

Name bit columns in such a way that that the value 1/true is the non default state of the domain. Set the default value to 0.

The rule really say: 0 is the most common value and 1 is the “unusual” state.

• Prefer IsDisabled over IsEnabled when the enabled state is most common
• Prefer IsUnknown over IsKnown when most values are known

Reverse the rule

This rule could be applied in reverse. Like left and right side driving – it’s a matter of picking one or the other.

Make bit columns NOT NULL and give them a default value

A lot of client application programmers struggle with NULL semantics. For bit columns, this becomes even harder to deal with, especially for languages that have the boolean data type. It is easier to simply disallow NULL bits

Prefer NOT NULL char/varchar with defaults of ” (the empty string) over NULL’able strings

The NULL semantics of string concatenation in database is not only inconsistent between database vendors (for example, compare the SQL Server and Oracle implementation) it is also looks nothing like what a non-SQL programmer expects to see. Because of this, it is generally easier to simply not use NULL strings when possible

Oracle DBA may choose to make the default a single spaces due to the odd implementation of empty strings in Oracle (which are undefined, not zero length strings). For a detailed discussion on this topic see: VARCHAR2: NULL value vs Empty String

For small character values (example, currenty symbols), disallowing NULL has the additional benefit for being more space and speed efficient.

Allow NULL in some form or another

There is a lot of theoretical discussion about the usage of NULL and 3-component logic. While it’s great to feel all smug in our DBA ivory towers – the reality is that programmers struggle with NULL semantics. Since there really isn’t much difference in what you can do either with NULL strings or without them – why not pick the least confusing implementation

Name the key of the table: ID_[table]

This makes it easy to auto generate/discover the name of the key column

Index Conventions

Indexes, unlike tables and column, should be prefixed with the type they are.

Indexes only become visible when tuning the database. When doing this, it is immensely convenient to be able to see the type of the index from the name

Constraint and Trigger Conventions

Trigger and constraints should be prefixed by their type

To avoid name clashing with tables and columns, it is useful to prefix the constraints and triggers in them

Name check constraints CK_[Table]_[Rule]

[Table] Is the name of the table the constraint belongs to. This helps avoid naming conflicts.

[Rule] is chosen in such a way that it describes what the check constraint does

Name foreign key constraints FK_[Table]_[Table Referenced]

[Table] Is the name of the table the constraint belongs to. This helps avoid naming conflicts.

[Table Referenced] is the table referenced. If you have followed the previous guidance, it is trivial to determine which column is pointed at.