Database Design Guidelines

Database Design Guidelines

Principles

• Support popular databases

Name Style

• Table Name
  Style: Pascal
  Example: Employee
  It is fine that you may use the singular style or plural style. But singular style is preferred here.

• Column Name
  Style: Pascal
  Example: HouseAddress
  Avoid to repeat table name in the column name, for example, For table 'Employee', use 'Name' for the column of employee name.

• Primary Key Name
  Style: Pascal, PK_${TableName}
  Example: PK_Employee

• Index Name
  Style: Pascal, UK_\({Column1Name}_\){Column2Name} for unique indexes.
  Use IK_\({Column1Name}_\){Column2Name} for non-unique indexes.
  Example: UK_Code

• Foreign Key Name
  Style: Pascal, FK_\({Column1Name}_\){Column2Name}
  Example: FK_DepartmentId

Columns

Text columns

• Do use unicode data type to define text columns.
• Do use nvarchar for variable length text columns.

Date/Date Time

• If the value of a date column is expected to present same text result among different time zone, use numeric or text data type to define date/date-time columns.
  If we define a column with date data type, default, most program language will use date-time type to store values from the column, we should realize the date value will be changed when the data is transited to a different time-zone. For example:
  2001/01/15, On the database server, timezone is +8.
  2001/01/15, On the application server, timezone is +8.
  2001/01/15 01:00:00, On the client, timezone is +9.
  So if it is a problem to you, try to use a integer or text data type to store the value.

Tips: consider the case carefully. ask these questions:
If your application will be used in different time-zone?
If you only use date part for the column?
If you only use time part for the column?

Nullable

• Avoid to define a column as nullable.
  Avoid to use null would reduce programing bugs, and bring better performance.

• Do Not use null for numeric columns if it has same meaning as zero.

• Do Not use null for text columns if it has same meaning as empty.

• May use null in a reference column provide that null is acceptable.

Keys

Primary Keys

• Do create a primary key for any tables.
• Recommend to use one column for the primary key.
• Use '${TableName}Id' for the name of the primary key column, the column is denoted as 'Id' columns.
• Recommend to use integer data type for the primary key column.
• Avoid change the value of the primary key column after created.
• It is acceptable to show the the value of the primary key column to customers if need.

Foreign Keys

• Do create a foreign key if there is a relation between 2 tables.
  It is useful to keep data integrity.
• Carefully use delete cascade clause when creating the foreign keys relationship.

Tips: Be careful, Use delete cascade is also dangerous. consider it twice before use it.

Database-specific constraints

• Avoid to use reversed words for name of objects of databases

Tips: please read the reversed words from database you will use

• Avoid to use system prefix in your object names
• Check length limitation of object names of databases

Tables

Entity Tables

Like departments, employee etc, we store these kind of information into entity tables.
In most case, the table like:

• Definition sample

Employee {EmployeeId, Code, Name, DepartmentId, ...}.

If in your system, an employee only exists in one department, you may use above definition.

Tree Tables

Like most organization, departments will be constructed as a tree, there are some top level departments (or is a root department), except these top level departments, departments must have one and only have one parent department.
We define this kind of table as:

• Definition sample

Department{DepartmentId, Code, Name, **ParentId**, ...}.

In most case, for the usage convenience, we will define a tree table for each tree relationship, denoted as a tree table.

• Definition sample
  DepartmentTree{ParentId, ChildId}.
  For a parent department, The table stores itself and all its descendants.
  From a view of a child department, the table stores itself and all its ancestors.

Hierarchical Tables

Different as tree tables, in the hierarchical tables, a child would have 0 to n parents.
For example, groups and users:

• Rules

  • an user would exists in multiple groups.
  • an groups would exists in multiple groups.

• Definition sample

User(userId, Name, isGroup)
GroupUser(parentId, userId)
GroupUserTree(parentId, userId)

The table GroupUser stores the direct relationships.
Instead, the table GroupUserTRee stores the redundant relationships like tree tables above.