Database Design Best Practices

How to design relational databases that are easy to work with and simple to maintain

Why Good Database Design is Important?

Good database design ensures data is consistent, minimizes duplication, allows high-performance access, and simplifies maintenance.

Define Your Entities

How to start a database design? First, decide what you will need to store.
Choose entities from your target domain.
For example, if you are designing a CRM application, your entities might include: contacts, customers, orders, products

Plan For Relationships

Analyze your target domain and discover relationships between entities.
What are relations in a database? Database relations are associations or links between tables, where the value in one's table field references to (usually) primary key in another table.
Note, there could be 3 relationship types:

  1. one-to-one
    In the case of a one-to-one relationship you often won't need a separate table, consider keeping data for both entities in one table.

  2. one-to-many
    In this case, a record in one table can be related to several records in another table. For our CRM example, you could find that orders are related to customers, as in one customer can have multiple orders, but one order is not usually placed by more than one customer. This means orders table should have customers_id column (foreign key)

  3. many-to-many
    Sometimes both entities could be linked in many-to-many ways, IE records from both tables could be linked to many records in another table. For our CRM example, orders and products have a many-to-many relationship, because one order can have several products, and one product can be included in several orders. To solve this, you could create orders_products "link" table that will store which product is included in which order (as well as storing additional information like ordered amount and price)

Normalization

Design your database to be in normal form using some formal rules like:

Note, in some cases, it's worth denormalizing DB structure (for example to increase performance), but that's out of the scope of this article.

CREATE TABLE template

There are several recommendations about standard fields/columns and standard names:

Why to use "i" prefix for icode/iname/idesc? Just because "code", "name" could be a reserved word in your database engine, addition of prefix simplifies the work with it. And "idesc" just to keep naming consistent.

Create Table Example (SQL Server style):

CREATE TABLE products (
  id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

  icode                 NVARCHAR(64) NOT NULL default '', -- product number
  iname                 NVARCHAR(64) NOT NULL default '', -- product title
  idesc                 NVARCHAR(MAX),                    -- product description

  status                TINYINT NOT NULL DEFAULT 0,        -- 0-ok, 10-inactive, 127-deleted
  add_time              DATETIME2 NOT NULL DEFAULT getdate(),
  add_users_id          INT NOT NULL DEFAULT 0,
  upd_time              DATETIME2,
  upd_users_id          INT NOT NULL DEFAULT 0
);

Data integrity

Performance

Maintenance

To keep things simple, put all your database structures into one file database.sql. It should have all the code necessary to initialize your database from scratch, and can include not only CREATE TABLE statements, but also:

Things to Avoid

  1. Avoid different names for the same type of fields. Keep field names consistent.

  2. Avoid using spaces or non-alphanumeric characters (underscore is OK) for table and field names. Such names require additional quoting to work with. Also, some systems/frameworks might not work well with such names.

  3. Avoid naming tables/columns/etc as reserved words. While it's possible with proper quoting, it's better just add a suffix or prefix. For example, instead of name column, you could name it as iname.

  4. Avoid business logic stored on the DB server:

Sometimes it's worth it to have logic above in the database. However, it's better to use a database just for data and keep all the business logic on the application level for easier development and maintenance.

  1. Avoid too many indexes per table.

Each index requires some effort from the DB server when data is added or updated. Also to be used efficiently, indexes need to fit into the server's memory. Therefore the more indexes you have, the more server resources are required.


If you need to have your SQL Server, MySQL, MS Access, or SQLite database properly designed - Contact Us and we will help you.