Database design (three) important designing rules which I follow

Original address: Https://www.codeproject.com/Articles/359654/important-database-designing-rules-which-I-fo

Spicy one?? , as if I have seen the Chinese version of this article, deteriorating, anymore ah, all kinds of plagiarism ah.

The original anchor point does not know is too long, or the format has a problem, here to change.

Table of Contents

• Introduction
• Rule 1:what is the nature of the application (OLTP or OLAP)?
• Rule 2:break your data in to logical pieces, make life simpler
• Rule 3:do not get overdosed with rule 2
• Rule 4:treat Duplicate Non-uniform data as your biggest enemy
• Rule 5:watch for data separated by separators
• Rule 6:watch for partial dependencies
• Rule 7:choose Derived Columns preciously
• Rule 8:do not being hard in avoiding redundancy, if performance is the key
• Rule 9:multidimensional data is a different beast altogether
• Rule 10:centralize Name Value table Design
• Rule 11:for Unlimited Hierarchical data self-reference PK and FK

Courtesy:image from Motion picturesintroduction

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

The reason I am writing a full blown article are, when developers design a database they tend to follow the three Normal forms like a silver bullet. They tend to think normalization are the only-to-be-from-designing. Due This mind set they sometimes hit road blocks as the project moves ahead.

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

Said and done normalization rules was important guidelines but taking them as a mark on stone was calling for Trou ble. Below is my own one 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 was the nature of the application you were designing for, is It transactional or analytical. You'll 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 is both kinds of applications:transaction based and analytical based, let's understand what these types AR E.

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

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

In other words if you think inserts, updates, and deletes is more prominent then go to a normalized table design, else c Reate a flat denormalized database structure.

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

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

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

For instance your can see the below table which have student names; If you ever want to query student names have "Koirala" and not "Harisingh", you can imagine what kind of a in a query you wil L end up with.

So the better approach would is to break this field into further logical pieces so we can write clean and optimal que Ries.

Rule 3:do not get overdosed with rule 2

Developers is cute creatures. If you tell them the the, 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'll operate on ISD codes of phone numbers separately (until your application demands it). So it would is a wise decision to just leave it as it can leads to more complications.

Rule 4:treat Duplicate Non-uniform data as your biggest enemy

Focus and refactor duplicate data. My personal worry about duplicate data isn't the IT takes hard disk space, but the confusion it creates.

For instance, with the below diagram, you can see "5th Standard" and "fifth standard" means the same. Now you can say the data have come into your system due to bad data entry or poor validation. If you ever want to derive a, they would show them as different entities, which are very confusing from the end user Point of view.

One of the solutions would is 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 simp Le foreign key.

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 is termed as "repeating groups". If we have to manipulate this data, the query would is complex and also I doubt about the performance of the queries.

These kinds of columns which has data stuffed with separators need special attention and a better approach would is to Mo ve those fields to a different table and link them with the keys for better management.

So-let's apply the second rule of 1st normal form: "Avoid repeating Groups". You can see in the above figure I has created a separate syllabus table and then made a many-to-many relationship with th e subject table.

The approach the Syllabus field in the main table are no more repeating and have data separators.

Rule 6:watch for partial dependencies

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

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

You can see how we've moved the syllabus field and attached it to the standards table.

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

Rule 7:choose Derived Columns preciously

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

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

This rule was also termed as the 3rd normal form: "No column should depend on other non-primary key columns". My personal thought is does not apply this rule blindly, see the situation; It's not this redundant data is always bad. If the redundant data is calculative data, see the situation and so decide if you want to implement the 3rdnormal form.

Rule 8:do not being hard in avoiding redundancy, if performance is the key

Do not make it a strict rule so you'll always avoid redundancy. If there is a pressing need for performance think about De-normalization. In normalization, need to make joins with many tables and in Denormalization, the joins reduce and thus increase PERFO Rmance.

Rule 9:multidimensional data is a different beast altogether

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

For such kinds of situations a dimension and fact design are a better approach. In simple words can create a simple central sales fact table which have the Sales Amount field and it makes a connectio n with all dimension tables using a foreign key relationship.

Rule 10:centralize Name Value table Design

Many times I have come across name value tables. Name and value tables means it has a 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 has a key and value.

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 has multiple sales people below them. For such scenarios, using a self-referencing primary key and foreign key would help to achieve the same.

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

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

You can also visit my website for step by step videos on Design Patterns, UML, SharePoint,. NET Fundamentals, VSTS, U ML, SQL Server, MVC, and lots more.

Database design (three) important designing rules which I follow