Common mistakes made while building and maintaining database

It is a common fault to use SQL Server management studio to create a database. This database has many options to reset size but as a common practise we go with the default sizing option. Apart from sizing in choosing data storage the log should also be sufficiently large if lot of insert or update operations have to be performed in your database. Make a rough estimate of the amount of data your project will need to store and only then choose the appropriate size.

This is the worst mistake that can be made yet it is the most common mistake. Backups are of three types full, differential, and transaction logs. The choice will depend on your customer’s requirement. It is not only important to take backup but plan storage of the backup in altogether different location or safety vaults. This area is mostly covered in the business continuity planning and disaster management practises adopted by many companies. It is also advisable to run the backup at times just to ensure that they will work in the hour of need. However, never commit the mistake of running it on your actual database, instead try in a test box or a testing environment.

No security

If you work in an organization you may realize how time critical may be the data. Always ensure that login to a database is password protected. Defining level of access is also important. Any tampering in data must be tracked with its user’s authorization into the system. Physical security of the database rooms is also important. Use access controls and make sure that referencing of the data is through a program. Only DBA should have the right to modify directly in the database. Database security is a bigger issue and is the new talk of the cyber security world. As a DBA programmer learning or knowing about database security is must.

Data integrity rules may be enforced by an application or database DDL as well. As a precaution database should also be designed to enforce some data integrity rules. Use constrains, properly define null values, and use foreign keys while carrying out the design.

People often feel that they can directly jump to entering data in database. This should never be done. Always make sure to plan and have a design in place before you can create a database. This will give your database structured approach and save you from the task of modifying the baseline structures. In case your organization decides to go from Business Intelligence or other solutions metadata dictionary will be easier to create. Structured data is therefore a good choice for future.

If you want your application to have high performance then indexing is a best practise should in corporate. It increases the speed of data retrieval. The only disadvantage is it eats some amount of your storage space. But, if the application is big then indexing is must.

It is a very common practice to use attribute 1 or abc as column or table name. This mistake seems silly but may eat up lot of your time. If the database developer leaves the organization then it is an absolute punishment for the new person.

As a database programmer everyone understands the importance of normalization but often forgets it while inserting data. Repeated values only use some useful space. It is not a good practise as normalization is an integral part of DBMS.

Databases today are user friendly and allow data entry directly. However, it is best to insert data with queries. Certain level of authorizations can be built in your database queries itself. Always ensure that you create a view for limited authorizations and do not give access to complete database.

When crisis situation strikes the first stage omitted is testing. Running individual queries is not enough. Stimulate a production like environment; bombard your database with data. Note performance, errors very carefully before giving a green flag to any database.