Organizations today must deliver tangible business value from their database investments. Adding to this challenge is the constant change inherent in complex, often diverse environments. From application and platform changes to increased user activity, such changes place added stress on both the database environment and the professionals charged with ensuring its peak performance.

Software Associates provides the breadth and depth of technical expertise required to meet the challenges presented by an ever-changing IT landscape. Software Associates leverages its prominent experts to provide best practices and proven techniques to help organizations maximize efficiency throughout the database development lifecycle.

Our approach to database management is not limited to database design alone. Along with it we offer further database management services. Our database management solutions life cycle is shown in figure 1-1.

We follow 80 to 85% of the world's best practices in database management. We provide database management solutions in different scales such as:

• Database Standardization
• Database Normalization
• Database Customization
• Database Migration
• Database Integration
• Backup & Restoration
• Repair of corrupted database
• Total Database Management

Our team has proven database skills in:

• Design normalized database as per project requirement.
• Stored Procedures.
• User defined functions.
• Triggers.
• Complex queries, especially "SELECT" queries using several tables; and JOINS – INNER,
• LEFT, UNION etc
• CURSORS, WHILE Loop etc for data iteration
• Temporary tables, @Table variables for temporarily storing table data for required contexts.

Our experience in database development has led us to develop a series of "best practices." Our goal is to create a set of guidelines that help organizations create usable, sustainable, and sharable databases. We follow the best practices in database management.

1) Anticipating the possibility of changes that may occur in the database we make our DB design strong enough to meet future requirements.

2) Implementing the 5 Ws of database design: who, what, when, where, why. We've got a logging and audit system to cover where and why and we do have a system that covers who, what and when too. And it has come in handy a few times. Every table in our database has a few special fields:

• Creation Time
• Created By
• Last Modified Time
• Last Modified By

These four fields are on every table and our application logic updates them appropriately.

3) We prefer to use stored procedures instead of 'inline queries' because of its features like:

• Precompiled execution
• Reduced client/server traffic
• Efficient reuse of code and programming abstraction
• Enhanced security controls

4) Strictly follow T-SQL standards.

5) Name/ Map entity tables considering OOP.

6) Do not use * in select statement.

7) Do not prefix "sp" in stored procedures.

Avoid Cursor, use forward; cursor only if necessary.

9) Avoid global temporary tables.

10) Avoid SQL Injection

11) Design Relational database in hierarchical structure.

12) Use GUID in tables for the distributed databases.

13) Provide encryption to ensure database security.

14) Provide encrypted fields in tables to ensure security of user credentials, if required.

How we deal with different Database Management Systems:

MS Access

SQL query

For most of the projects we had used the in' line queries' but now we implement stored procedures as it is now available on MS Access

Parameters

We have strictly used parameters instead of concatenating the values to generating DB queries. This, we feel, is the best approach and practice, although we find most of the developers still using the concatenating method.

This approach has helped us to reduce the development time to handle the sql injection issues, which is the main head- ache for Access development.

Table management

Despite all the management facilities available to manage Table and data in MS Access, most of the time we used Queries to create tables and in bulk data management scenarios. Through this approach, we could easily manage database which is working online, without interrupting the System process.

MS SQL

SQL 2005

We have implemented most of the features provided by MS SQL 2005

• Stored Procedure
• Functions
• Views

We have also followed the MS recommended T-SQL standards.

DB Design

Rather than following pre-defined patterns we have focused on the customer's requirement and value thus reducing development time.

Query building

We have reduced the use of methods and commands (like CURSOR, Temporary Tables), which use more response time and memory and affect the whole system process.

Role based management

The plethora of role based data management features provided by the SQL server have been effectively used while connecting to online database. This helped implement effective security in db management.

MySQL

We use PhpMyAdmin for database development & management.
Future time expansion is considered during database design phase.
Direct modification of the live database is not permitted in most cases. Modifications are done through scripts.
Direct export of database is not allowed. Export is possible only by using Scripts.

Data management with MS Office

Most of our work includes the conversion of the manual process to online. So at the database design time we have to consider the customer's existing data, and start with those.

At these times we found that most of our clients did their daily work with the help of Excel spread sheet for data storage and management. So to start with these data, we had to migrate the data to our New Application's database first.

We called this process "bulk data uploading", which we successfully achieved with the help of OLEDB management classes. To achieve this, we developed an application for gathering the data from Excel and uploading it to the Database.

Then we added a feature to the application for a client, by which they can download whole database table data in an excel sheet. Clients can also upload bulk data to the Application system through excel sheet entry, where we provided proper v alidation and error messages for each column records.