Best practices in database designing

Every developer knows that in a long term project, a poor database design, inconsistent naming conventions or un-optimized stored procedures can come back to haunt you; Unfortunately their affects are sometimes not immediately apparent , especially in the initial design phase. But believe me when I say that they can lead to nightmares (literally) later.

The article lists some such “best practices” to help you avoid these “situations”. This is not an exhaustive list and I am sure “industry experts” will keep coming up with newer and better “best practices”, it is a list of guidelines that I personally have found useful and have kept adding to and refining over the years.

• Proper data type :-

More often than not we choose the default basic data types like INT, NUMERIC or DATETIME. This can lead to a bloated database with space and performance issues. Consider data types like TINYINT, SMALLINT and SHORTDATETIME during your design. Be aware of the data range for each of them and make the decision accordingly, for example if you don’t expect a leave application to list more than 256 possible reasons (how many official reasons can you think of, for not coming to the office) you would keep the primary key for the table as TINYINT instead of INT.

• Proper naming convention:-

Proper naming convention is probably the most important factor affecting the maintainability of a database. Imagine looking for an obscure procedure name like “CreateclientDefault_single”, and I assure you, this is a real life example. I bet we will get five different guesses from five different people on what the procedure does, from the name, On the other hand if I ask you to guess the purpose of a procedure called Proc_CreateDefaultUserForClient, almost everyone will say the same thing. It is also advisable to prefix each database object by a prefix which denotes the object type, that way you won’t always need to open an object explorer to find a DB object, you can find it right from the query browser.

• Proper constraints:-

Always try to define proper constraints which will avoid anomalies in database. Try to define primary key, foreign key, UNIQUE key and other constraints. Just keeping the column names same is not enough, as your database grows larger and new entities are introduced; it is usually easier to make the DBMS validate your data, rather than introducing business logic to do the same.

• Normalization:-

I am not going to get into the details of normalization practices and its advantages. It is too big a topic to cover here. Normalization is unequivocally a “best practice” and should be applied whenever possible. What I do want to stress is that it should be taken with a grain of salt; although very few I have come across some instances when there was a justification for not applying further normalization to table. Remember normalization is driven by the need to reduce redundancy and maintain consistency. If there is a reason that you need to keep consolidated data in a single table and any al may be desirable.

• Indexes:-

Defining index is one of the great challenges itself. Although by creating index your data fetch process will be fast but your other DML operation like INSERT, UPDATE, DELETE will face some performance issue so be more specific while designing index. There is tool available with Microsoft SQL Server Management studio which helps in defining the indexes. As a broader look always try to create index of integer and numeric. Try to avoid index on date time and String.

• Stored procedures & functions:-

As we know stored procedures and functions are the best way to fetch data. We can write multiple SQL statements in stored procedures and functions another benefit is stored procedures are compiled statements so the chances of error is less.

Other than this there are many more things which will help in improving database performance which we can include in best practices. I will share those key notes in my next post.