Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

11 important database designing rules which I follow

Rate me:
Please Sign up or sign in to vote.
4.80/5 (112 votes)
25 Feb 2014CPOL9 min read 906.5K   243   44
This article will discuss about 11 important database designing rules.

Table of Contents

Image 1

Courtesy: Image from Motion pictures

Introduction

Before you start reading this article let me confirm to you I am not a guru in database designing. The below 11 points are what I have learnt via projects, my own experiences, and my own reading. I personally think it has helped me a lot when it comes to DB designing. Any criticism is welcome.

The reason I am writing a full blown article is, when developers design a database they tend to follow the three normal forms like a silver bullet. They tend to think normalization is the only way of designing. Due this mind set they sometimes hit road blocks as the project moves ahead.

If you are new to normalization, then click and see 3 normal forms in action which explains all the three normal forms step by step.

Said and done normalization rules are important guidelines but taking them as a mark on stone is calling for trouble. Below are my own 11 rules which I remember on the top of my head while doing DB design.

Rule 1: What is the nature of the application (OLTP or OLAP)?

When you start your database design the first thing to analyze is the nature of the application you are designing for, is it Transactional or Analytical. You will find many developers by default applying normalization rules without thinking about the nature of the application and then later getting into performance and customization issues. As said, there are two kinds of applications: transaction based and analytical based, let’s understand what these types are.

Transactional: In this kind of application, your end user is more interested in CRUD, i.e., creating, reading, updating, and deleting records. The official name for such a kind of database is OLTP.

Analytical: In these kinds of applications your end user is more interested in analysis, reporting, forecasting, etc. These kinds of databases have a less number of inserts and updates. The main intention here is to fetch and analyze data as fast as possible. The official name for such a kind of database is OLAP.

Image 2

In other words if you think inserts, updates, and deletes are more prominent then go for a normalized table design, else create a flat denormalized database structure.

Below is a simple diagram which shows how the names and address in the left hand side are a simple normalized table and by applying a denormalized structure how we have created a flat table structure.

Image 3

Rule 2: Break your data into logical pieces, make life simpler

This rule is actually the first rule from 1st normal form. One of the signs of violation of this rule is if your queries are using too many string parsing functions like substring, charindex, etc., then probably this rule needs to be applied.

For instance you can see the below table which has student names; if you ever want to query student names having “Koirala” and not “Harisingh”, you can imagine what kind of a query you will end up with.

So the better approach would be to break this field into further logical pieces so that we can write clean and optimal queries.

Image 4

Rule 3: Do not get overdosed with rule 2

Developers are cute creatures. If you tell them this is the way, they keep doing it; well, they overdo it leading to unwanted consequences. This also applies to rule 2 which we just talked above. When you think about decomposing, give a pause and ask yourself, is it needed? As said, the decomposition should be logical.

For instance, you can see the phone number field; it’s rare that you will operate on ISD codes of phone numbers separately (until your application demands it). So it would be a wise decision to just leave it as it can lead to more complications.

Image 5

Rule 4: Treat duplicate non-uniform data as your biggest enemy

Focus and refactor duplicate data. My personal worry about duplicate data is not that it takes hard disk space, but the confusion it creates.

For instance, in the below diagram, you can see “5th Standard” and “Fifth standard” means the same. Now you can say the data has come into your system due to bad data entry or poor validation. If you ever want to derive a report, they would show them as different entities, which is very confusing from the end user point of view.

Image 6

One of the solutions would be to move the data into a different master table altogether and refer them via foreign keys. You can see in the below figure how we have created a new master table called “Standards” and linked the same using a simple foreign key.

Image 7

Rule 5: Watch for data separated by separators

The second rule of 1st normal form says avoid repeating groups. One of the examples of repeating groups is explained in the below diagram. If you see the syllabus field closely, in one field we have too much data stuffed. These kinds of fields are termed as “Repeating groups”. If we have to manipulate this data, the query would be complex and also I doubt about the performance of the queries.

Image 8

These kinds of columns which have data stuffed with separators need special attention and a better approach would be to move those fields to a different table and link them with keys for better management.

Image 9

So now let’s apply the second rule of 1st normal form: “Avoid repeating groups”. You can see in the above figure I have created a separate syllabus table and then made a many-to-many relationship with the subject table.

With this approach the syllabus field in the main table is no more repeating and has data separators.

Rule 6: Watch for partial dependencies

Image 10

Watch for fields which depend partially on primary keys. For instance in the above table we can see the primary key is created on roll number and standard. Now watch the syllabus field closely. The syllabus field is associated with a standard and not with a student directly (roll number).

The syllabus is associated with the standard in which the student is studying and not directly with the student. So if tomorrow we want to update the syllabus we have to update it for each student, which is painstaking and not logical. It makes more sense to move these fields out and associate them with the Standard table.

You can see how we have moved the syllabus field and attached it to the Standards table.

This rule is nothing but the 2nd normal form: “All keys should depend on the full primary key and not partially”.

Rule 7: Choose derived columns preciously

Image 11

If you are working on OLTP applications, getting rid of derived columns would be a good thought, unless there is some pressing reason for performance. In case of OLAP where we do a lot of summations, calculations, these kinds of fields are necessary to gain performance.

In the above figure you can see how the average field is dependent on the marks and subject. This is also one form of redundancy. So for such kinds of fields which are derived from other fields, give a thought: are they really necessary?

This rule is also termed as the 3rd normal form: “No column should depend on other non-primary key columns”. My personal thought is do not apply this rule blindly, see the situation; it’s not that redundant data is always bad. If the redundant data is calculative data, see the situation and then decide if you want to implement the 3rd normal form.

Rule 8: Do not be hard on avoiding redundancy, if performance is the key

Image 12

Do not make it a strict rule that you will always avoid redundancy. If there is a pressing need for performance think about de-normalization. In normalization, you need to make joins with many tables and in denormalization, the joins reduce and thus increase performance.

Rule 9: Multidimensional data is a different beast altogether

OLAP projects mostly deal with multidimensional data. For instance you can see the below figure, you would like to get sales per country, customer, and date. In simple words you are looking at sales figures which have three intersections of dimension data.

Image 13

For such kinds of situations a dimension and fact design is a better approach. In simple words you can create a simple central sales fact table which has the sales amount field and it makes a connection with all dimension tables using a foreign key relationship.

Image 14

Image 15

Rule 10: Centralize name value table design

Many times I have come across name value tables. Name and value tables means it has key and some data associated with the key. For instance in the below figure you can see we have a currency table and a country table. If you watch the data closely they actually only have a key and value.

Image 16

For such kinds of tables, creating a central table and differentiating the data by using a type field makes more sense.

Rule 11: For unlimited hierarchical data self-reference PK and FK

Many times we come across data with unlimited parent child hierarchy. For instance consider a multi-level marketing scenario where a sales person can have multiple sales people below them. For such scenarios, using a self-referencing primary key and foreign key will help to achieve the same.

Image 17

This article is not meant to say that do not follow normal forms, instead do not follow them blindly, look at your project's nature and the type of data you are dealing with first.

Image 18

Below is a video which explains the three normal forms step by step using a simple school table.

Image 19

For further reading do watch the below interview preparation videos and step by step video series.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect https://www.questpond.com
India India

Comments and Discussions

 
QuestionExample Pin
Member 1481840530-Apr-20 6:22
Member 1481840530-Apr-20 6:22 
Question11 important database designing rules which I follow Pin
XolaniG19-Jun-17 21:08
XolaniG19-Jun-17 21:08 
QuestionMS SQL Server developer download Pin
Member 1254199324-May-16 0:33
Member 1254199324-May-16 0:33 
Generalnice Pin
BillW332-Mar-16 7:37
professionalBillW332-Mar-16 7:37 
QuestionGreate Pin
Aladár Horváth18-Aug-15 1:27
professionalAladár Horváth18-Aug-15 1:27 
Bug[My vote of 2] rule 10 is one of common database design mistakes. Pin
seyyed hamed monem21-Jun-15 21:53
professionalseyyed hamed monem21-Jun-15 21:53 
hi,
rule 10 is one of common database design mistakes.
see:
https://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/[^]
https://www.simple-talk.com/sql/database-administration/five-simple--database-design-errors-you-should-avoid/[^]
https://decipherinfosys.wordpress.com/2007/02/01/otlt-one-true-lookup-table/[^]
http://tonyandrews.blogspot.ca/2004/10/otlt-and-eav-two-big-design-mistakes.html[^]
http://sqlmag.com/database-administration/designing-performance-lookup-tables[^]
http://sqlblog.com/blogs/louis_davidson/archive/2010/11/20/one-domain-table-or-many.aspx[^]
GeneralMy vote of 5 Pin
Pratik Bhuva30-Mar-15 23:41
professionalPratik Bhuva30-Mar-15 23:41 
Questiondesign Pin
Member 1121847518-Nov-14 1:02
Member 1121847518-Nov-14 1:02 
GeneralRule #10 has to be put down Pin
opc318-Sep-14 15:31
opc318-Sep-14 15:31 
AnswerRe: Rule #10 has to be put down Pin
Anurag Gandhi17-Aug-16 0:19
professionalAnurag Gandhi17-Aug-16 0:19 
GeneralMy vote of 5 Pin
Akiii_Lethal25-Jul-14 0:03
Akiii_Lethal25-Jul-14 0:03 
GeneralGood one Pin
Shahriar Iqbal Chowdhury/Galib8-Jun-14 22:28
professionalShahriar Iqbal Chowdhury/Galib8-Jun-14 22:28 
QuestionMy vote of 5, but one doubt Pin
Thava Rajan28-Feb-14 0:21
professionalThava Rajan28-Feb-14 0:21 
QuestionGood Article Pin
AzuriVN27-Feb-14 17:03
professionalAzuriVN27-Feb-14 17:03 
GeneralMy vote of 5 Pin
David Days27-Feb-14 6:57
professionalDavid Days27-Feb-14 6:57 
Questionhere a question is here! what do you think? which decision you will made? Pin
Phu Hiep DUONG26-Apr-13 11:05
Phu Hiep DUONG26-Apr-13 11:05 
QuestionRule 8: Do not be hard on redundancy Pin
Jan Zumwalt30-Nov-12 7:19
Jan Zumwalt30-Nov-12 7:19 
GeneralMy vote of 5 Pin
zeego13-Nov-12 20:18
zeego13-Nov-12 20:18 
GeneralMy vote of 5 Pin
Xavi Rius11-Sep-12 11:24
Xavi Rius11-Sep-12 11:24 
GeneralMy vote of 5 Pin
Jasmine250110-Sep-12 10:49
Jasmine250110-Sep-12 10:49 
GeneralMy vote of 2 Pin
Member 85284097-Sep-12 11:02
Member 85284097-Sep-12 11:02 
GeneralMy vote of 4 Pin
brother.gabriel28-Apr-12 11:45
brother.gabriel28-Apr-12 11:45 
GeneralMy vote of 4 Pin
Uilleam9-Apr-12 5:39
Uilleam9-Apr-12 5:39 
GeneralRe: My vote of 4 Pin
Shivprasad koirala10-Apr-12 0:58
Shivprasad koirala10-Apr-12 0:58 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey5-Apr-12 7:45
professionalManoj Kumar Choubey5-Apr-12 7:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.