Naming Conventions

General

When designing a database schema, it is wise to follow some basic rules as sticking to these conventions makes it much easier to read the SQL statements and to automatically apply changes to the whole schema via code.

Of course there will always be exceptions to the rules, but following the "Convention over Configuration" paradigm saves in the end a lot of overhead.

Tables

• Tables should be named in singular form, for example 'Product' instead of 'Products'.
• Avoid all kinds of abbreviations and implicit domain knowledge in table names; don't use 'CstInvNo' for 'CustomerInvoiceNumber', for example.
• Each table should have a field named Id, which is usually an auto-incremented value and is used as unique identifier for each record.
• Most tables will have a field named Name, which contains a short description of the record.

SELECT Invoice.*, Product.*
  FROM Invoice
  JOIN Product
    ON Invoice.ProductId = Product.Id

As you can see in line 4 the SQL statement is easier to read and sounds more consistent than something like 'Invoice.ProductId = Products.ProductId', which would at best contain some redundant information and at worst would be misleading aside inducing possible misspellings.

Lookup Tables

Lookup Tables usually contain only a few records and serve as master data table for detail tables. A table with salutations ("Mr", "Mrs", "Ms") is such a table. The name of the lookup table is usually as short as possible and descriptive as necessary.

Detail Tables

Detail tables contain the essence of a database.

Intersection Tables

(check for other terms) - tblProduct, tblColor, tblProductColor

Fields

• Fields should be named in singular form as well.
• Try to stick with the term Id when it comes to key fields, avoid a mixup of "Id", "Number", "Key", "No" and all other key-like terms.
• The data type of the field should be tailored to its intended use.
  • Example: If you have a field which contains ISO currency codes, you will not expect to have more than three alphabetic characters in there (in fact you will not expect it to have less than three either), thus you should choose the size of the field accordingly. By doing this you will not waste space and create "implicit validity", as you will not be able to enter four-character ISO currency codes into this field.

Indices

(summarize fields and / or purpose)

Referential Integrity

• constraints
• primary key
• foreign key