Dear
All,
First
of I would like to wish you all a very happy Sravan Month. Om Namah Shivay!
It
is long gap between you and me via blog as I was busy with some other tasks.
But I am sure you people were visiting my blog and I would like to hear your
feedback/suggestions.
Here
today one of my friend (students) wanted to know some tips about database designing,
here I would like share you few tips which I used to apply in my projects and
little experience I have in my past experience.
Database
designing is main task and very important for every developer to startup the
projects. As our bodies main function work from our soul same like our dynamic
web applications works with the help of our DATABASE. In simple word if our
soul is happy then our body will also be happy else vice versa. So if you want
to make your application in proper way then you should mainly focus on your
database.
Here
are the main points which we need to focus on while creating database in SQL
·
Know
your requirements before designing the database (how many tables required)
·
Now
once you know about Tables, you should think about columns (how many columns
required).
·
Avoid
unnecessary columns
·
Know
the proper Data Type for column i.e. should not use varchar for storing numbers
·
Each
Tables should have at least one primary column with identity
·
Use
varchar or char wherever required to store string value instead of using
nvarchar, nchar as it takes almost double byte space in compare to varchar or
char
·
Create
Indexes for required columns
·
Use
Normalization for tables (at least 3NF)
Here
is an example:
Bad Database Design
Student
Table:
|
StudentID
|
Name
|
Standard
|
Division
|
Registration
|
|
1
|
Mukti
|
10
|
A
|
2015-08-02
|
|
2
|
Ranjan
|
Twelfth
|
B
|
2015-08-02
|
Here
in above sample few mistakes we can avoid to make our database in proper
manner:
·
StudentID:
Should be primary key with identity column
·
Standard:
As we can see there are 10, Twelfth so it is more confusing to store data in
column
·
Division
and Standard should be in other table to manage (Normalization) to maintain
data properly (to avoid duplicate values)
Good
Database Design
Student
Table:
|
StudentID
|
Name
|
StandardID
|
DivisonID
|
Registration
|
|
1
|
Mukti
|
1
|
1
|
2015-08-02
|
|
2
|
Ranjan
|
2
|
2
|
2015-08-02
|
Standard
Table:
|
StandardID
|
Standard
|
|
1
|
10
|
|
2
|
12
|
Divison
Table:
|
DivisionID
|
Divison
|
|
1
|
A
|
|
2
|
B
|
I
think it will help you out to design database in very simple way. This is my
own experience and practice so nothing is related to anything.
Cheers,
Ved
Pathak
Comments