Microsoft SQL Server

Database Design

Version - SQL Server 2008
Client based in – India

The Challenge

For our client, handling and managing the business processes involved a lot of manual work which was tedious, error- prone and time consuming. The client suffered delays in making deliveries to the customers, which were danger for the client's image. The client wanted us to develop an easy to use ERP that would automate their business processes and help eliminate the cons of manual work. Giving the much obvious benefits of SQL Server, we decided to use it in for the back-end.

The Solution

Primary aspect that comes in mind while working with data is security. We used SQL Server’s role based security to prevent unauthorized access to the database. We analysed client’s business processes, and required permissions and roles were decided.

Next thing was to decide for the tables to be used, we created appropriate normalized tables so as to eliminate data redundancy. An important aspect that demanded our attention here was – on which tables to create index and when to create them. Indexes when used optimally provide a significant performance boost. Other minute details like primary keys and foreign keys were taken care of to ensure integrity of data.

After that, there was the turn to design stored procedures which would be used to access /modify the data. All the business operations of the client were grouped logically and relevant stored procedures were designed to reduce server round trips.

Transactions were used to ensure that data was not corrupted in cases of failures like power off or I/O failures.

A data purging mechanism was also designed so as to save resources occupied by unwanted historical data.

The system is running smoothly and the client has not complained about the performance issues so far.