SQLINFO – We play with data and technology

Database Design:

As you all know, database design is a very basic need in a software development. I heard from lot of people that database programming and design can be handled by anyone who has some little experience in database. I am sure that it is a mistake and a strong mistake.

Why I am saying it is a mistake?

Developing software without a proper database design is similar to building a Multi-storied building with no proper foundation. Multi-storied building will collapse once it is occupied or some floors are added to the existing building. The same scenario will happen here also. I will show how it happens with one simple example.

This is one example I got from the real world scenarios. I am forced to see lot of tables without even a primary key. This is one of the basic things which are explained in the database design normalization rules.

 

Here I am going to explain a small scenario which will hit the Performance.

Let’s look this example:

-- Simple Example of coosing DataTypes
CREATE TABLE test4u
  (
     id  UNIQUEIDENTIFIER,
     id2 NVARCHAR(MAX)
  )

INSERT INTO test4u
SELECT Newid(),
       Newid()

SELECT *,
       Datalength(id)  AS [No of bytes Used for ID],
       Datalength(id2) AS [No of bytes Used for ID2]
FROM   test4u

Look at this scenario,

We are inserting two GuiD values in two columns ID and ID2 and their data types are uniqueidentifier and nvarchar(max). Here I am displaying the no of bytes used to store the data. For ID Column we used unique identifier which uses less bytes than ID2. So storing a GUID value in uniqueidentifier is most recommended method. This will really improve the performance due to the less Memory Utilization. After database comes into production, we can’t change the DB structure since it will affect DB and Application codes.

This is only a very simple example which helps others to think about the database in a Performance point of view. A lot of things will improve the performance. So proper database design is a  very basic need in every project.

 

What is agile methodology?

One line difference for Agile and Waterfall model:

When the client is not clear with requirements we can go for agile methodology.When the client is clear with his requirements we can go for waterfall model.

Developing a database in agile environment:

Here there is a challenge for the development DBA’s. I will explain with a small scenario, which I understood from the internet and books I learned. Some of the links I am posting here. Thanks to john Brady an Oracle database specialist (20 years of Experience), who explains about the agile database development in his blog databaseperformance.blogspot.com.

Requirements:

• The team should contain Database Administrators/Architects/Developers who has knowledge of agile database design techniques.
• Application team should have a thorough knowledge of what they are going to do in each iteration.
• Programmers, Team leads and PM should  work closely with DBA’s
• DBA’s should know through knowledge of Database performance Tuning.

Implementation:

• DBA’s should design Database as if database will change in Future rather than we are not clear with the requirements. The Question is how DBA’s will think like that?
• Look at this simple example,  you will be able to see what happens in each iteration  through database diagrams.

Example requirement:

• 1^st Iteration:  An sms/voice-call (Not Confirmed by Client) should sent to the students and Staffs once they will enter into the college to make them alert that they reached the campus premises.
• 2^nd Iteration: Students parents also should be alerted while entering the college.
• 3^rd Iteration: Finalized that SMS should Sent.

How we will design database with Agile Methodology:

1^st Iteration:

Database Diagram for 1^st Iteration:

2^nd Iteration:

Database Diagram for 2nd Iteration:

3r^d Iteration:

Database Diagram for 3rd Iteration:

References: http://www.agiledata.org/essays/bestPractices.html

References: http://databaseperformance.blogspot.com/2009/10/challenge-of-agile-database-design.html