Database Design Best Practices

how to design relational database that are easy to work with and simple to maintain

Why Good Database Design is Important?

Good database design ensures data to be consistent, minimizes duplication, allows high-performance access as well as simplifies maintenance.

Define Your Entities

First, choose entities you will store in your database from 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. Note, there could be several relationship types:

  1. one-to-one
    In the case of a one-to-one relationship you often won't need a separate table, so 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 related to customers, so one customer can have multiple orders. But usually, one order cannot be placed from 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, i.e. 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 included in which order (as well as additional information could be stored 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

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

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.
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 some 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 its worth 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 efforts for the DB server when data 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 required.


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