Md. Obaidul Haque Sarker on Database Design

Answers to database design questions by Md. Obaidul Haque Sarker, Dhaka, Bangladesh
Date: 29 December 2014

Please explain (or briefly describe) the general philosophy behind your database design approach.

     I follow SDLC approach in software development. There are several phases in SDLC such as requirement analysis, design, coding, testing and deployment. I am doing database design in design phase of the SDLC. Requirements are listed in software requirement specification (SRS) document in requirement analysis phase. SRS document is written by the system analyst (SA) or business analyst (BA).
     I follow the top-down approach the database design. I study the SRS document for software requirement. I find entities from SRS document and also look relationship among the entities .The top-down design method starts from the general and moves to the specific. In other words, I start with a general idea of what is needed for the system and then work your way down to the more specific details of how the system will interact. This process involves the identification of different entity types and the definition of each entity’s attributes.
     In summary, I follow the following steps in database design.
     1. Find entities of the whole system
     2. Find attributes of each entity
     3. Find relationship between/among entities
     4. Find unique key or primary key of each entity and relationship
     
     I draw the E-R diagram and data flow diagram (DFD) for data model. Customer/ end-user can see the E-R diagram for better understanding of their software.

Where do you live?

     I live in Dhaka, Capital of Bangladesh.

Briefly describe your pertinent work and/or educational background.

     I have completed my bachelor and master degree in Computer Science. I have over 10.0 years of experience as a software engineer and database designer.
     Over the last 10 years, I have developed 30+ desktop and web applications using VB 6.0, VB.net, C#.net, ASP.net web form and MVC, Entity Framework, Crystal Report, RDLC report, Java script, JQuery, Ajax, PHP, JQuery, SSRS, SSAS and SSIS.
     I also have experience in database installation and configuration, design, performance tuning, backup and recovery, and database migration. I am also Oracle Certified Professional (OCP) DBA.
     Expertise in database design and implementation of business logic in database level (triggers, procedures functions and packages) in Oracle, MS SQL, MySQL, Sybase ASE and PostgreSQL database.
     Writing technical document (Software Requirement Specification (SRS), High Level and Low level design document, Test case document). Good analysis, design and problem solving skill.
     Worked in various applications such as Human Resource Management, Payroll, General Accounting, Provident Fund, Gratuity Fund, Inventory Management, Library Management, Fixed Asset Management, University Management, and Project Information Management
     In my job tenure, I have done crucial projects on Governance, banking, pharmaceuticals, garments, NGO, education & telecom industry.

What are the industries you have the most experience working within?

     I have over 10.0 years of experience as a software engineer in software design and Development Company.
     In my job tenure, I have done crucial projects on Governance, banking, pharmaceuticals, garments, NGO, education & telecom industry. I have worked in domain areas such as Human Resource Management, Payroll, Accounting, and University Management. In most of the projects, I was involved in database design and development.

Your rate is higher what other programmers and freelancers charge. How does what you do with database design save clients and companies money in the long run?

     I must do optimum database design before coding started. Once it is done in perfectly, it minimizes software maintenance cost in future. It also increases the performance of database. I also write the database stored procedures, functions and packages in the optimized way. It increases the performance of the database.
     If database are not designed in perfectly, then it will be more costly and complex when new functionality add to the system.

You have special expertise in database design. How is what you do different from what a generalist computer programmer might do when it comes to creating a database back end?

     A computer programmer always thinks the coding approach of the software. Their design meet the functional requirement of the software, but not non-functional requirements such as performance, adaptability. But they don’t know better way to design the database.
     A professional database designer or DBA design the database in optimized way. A DBA knows better way to design the database. So, DB design professional rate is higher.

When you look at database design work done by generalist programmers or other people with limited DB design expertise, what is the most insane or annoying thing you see repeatedly?

     I review the database design which is done by a generalist programmer. I review for both the functional and non-functional requirements that must be met. But, they are missing always non-functional requirements such as performance issue. Some programmers designed database is not properly normalized. The review session is done before coding. I recommend the programmer incorporating the review findings.

How is the database design work you do distinct from and also integrated with other aspects of overall software/technology systems?

     Database design is the crucial part of any new database application development. Database designer must understand domain knowledge (i.e. software requirements) of the application, and also should have DB design experience. DB designer read SRS or BRS document for domain knowledge. DB design is done in design phase of the software development life cycle (SDLC) process.

Describe how the database design work you do now is similar to or different from "textbook database design," such as one might learn in college or read in a text book or software manual.

     I read lot of text book for database design. All of text book contain the basic knowledge about database such as entity, relationship, constraints, E-R diagram, normalization etc. Those information are scattered in different chapter in a book or in different book. But I can't get any real life scenario from text book where all of features are implemented together.

What is the biggest example of a "rule" you routinely break? (Or another way of putting it is: what popular tradition are you most likely to depart from, if any?)

     I have about 10+ years experience in database design and development. Initially [before 5 years], I forgot to create the indexes and foreign key constraints during database design. But Now I am doing all of things during DB design. I prepared a checklist for DB design. I review the design as per my checklist. If anything I missed, I will incorporate it before coding started.

What RDBMS have you used extensively, or are at least somewhat familiar with based on first-hand experience?

     I worked with Oracle RDBMS in mostly in my service tenure. But I also worked with other RDBMS like MSSQL, MySQL, PostgreeSQL, Sysbase and DB2.

Given an assignment to create a website from scratch for Company X, what RDBMS are you using? (website specs: principle table has 10,000 rows and will grow to 200,000 over next five years; admin back-end, general browsing, user logins; no more than 100 concurrent users)

     I will prefer to use MySQL RDBMS for the above assignment.

What other technologies are you using for this website?

     Alternatively, I prefer to use following technologies
     Database: MSSQL
     Language: ASP.net MVC
     Others: CSS, JQuery, Ajax.

What tools would you be using? (IDEs, code editors, database design tools, if any)

     I am using the following tools –
     1. Microsoft Visual Studio
     2. WampServer
     3. Eclipes
     4. SQLYog
     5. Toad for Oracle
     6. Toad for MySQL
     7. Microsoft SQL Server Management Studio
     8. MySQL Workbench
     9. Microsoft Visio

Aside from the database design, how much other development do you typically do? How much would you prefer to do?

     I design database, but I am also a programmer. I write code in .Net and PHP.

Table names: plural? singular? or something else? why?

     A table contains one or more tuples/rows/records. Each row means a single entity.
     For example, Student information management database contain the student profile. Each student is a single entity. But all students information are stored in a table called Students. Here “Students” is a table name, and which is plural. But if a table contains only one record, it will be singular form.

How do you name your primary identity fields?

     I follow the naming convention of each entities and attributes. Primary key may be single field or multiple fields. Normally, I follow the single field for primary key which is incremental value. Other fields which identify the row in a table uniquely, I create a composite unique key constraint in that case.

Describe your relationship (if any) with WordPress?

     I am new in Wordpress database.

If you use stored procedures, how do you decide to use them or not use them? What are your alternatives?

     Stored procedure is good when you need to do some complex queries and calculation in database. All queries are embedded in a single stored procedure which is called by the application in once. But if you execute each query in separately, it is very costly. It degrades the performance too.
     If you want to execute single SQL statement in application, you don’t need to write stored procedure.

Why would a client who needs database design work done be making a wise move to hire you (if you're available)?

     Stored procedure is good when you need to do some complex queries and calculation in database. All queries are embedded in a single stored procedure which is called by the application in once. But if you execute each query in separately, it is very costly. It degrades the performance too.
     If you want to execute single SQL statement in application, you don’t need to write stored procedure.