The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Optimizing database design

*** This question has been posted in one other forum also. ***

I am developing Payroll Solution for a large government department. I have created following tables:

Table: Allowance Master (AllowanceID smallint P.K, AllowanceName varchar(30) unique)

Table: DeductionMaster (DeductionID smallint P.K, DeductionName varchar(30) unique)

Table: EmpMaster (EmpCode LongInt P.K, EmpName varchar(60))

Table: Emp_Pay_Details (Salary_Month_Year Date, Emp_Code, Tran_Detail varchar(30), Tran_Type char(1), Amount number(20,2))

In Emp_Pay_Details table, the TranDetail field stores the name of Allowance/Deduction and TranType field stores A or D, for Allowance or Deduction. The sample records will look something like this:

SALARY_MONTH_YEAR      EMPCODE    TRAN_DETAIL      TRAN_TYPE    AMOUNT
-----------------------------------------------------------------------------------------------------------------------------------
Jan-2007                                101                DEARNESS        A                    100
Jan-2007                                101                VEHICLE            A                      200
Jan-2007                                101                RECOV.              D                      5000

As you can see in the above table, the pay record for the month Jan-2007 is entered. Though I have shown only three rows. In real there are compulsorily 15 rows for each employee. These 15 rows shall be entered per month for each employee. Though we have taken Allowance Master table and Deduction Master table where we will store one time values for the total number of Allowances/Deductions that need to be applied on the month salary. The Emp_Pay_Details table stores the exact record of the salary.

In the Emp_Pay_Table, I have used Tran_Detail field to store either the name of Allowance (from Allowance Master table) or the name of Deduction (from Deduction Master table) and the Tran_Type column stores A or D for Allowance or Deduction.

Since this table will store 15 records for each employee per month and there are 5000 employees, so 15*5000 = 75,000 rows shall be inserted each month. Depending upon the growing size of the table, I want to know:

(1) How to optimize the table structure for better performance?

(2) Whether I should take a composite key of (Salary_Month_Year, EmpCode, Tran_Detail) as Primary Key or any other way?

(3) I am using SQL Server 2005. Any feature that may optimize fetching records from this table.
RK Send private email
Sunday, August 12, 2007
 
 
You can keep the data model the way it is. 75000 records per month is not much. If it ever becomes a problem you can use Table Partitioning over the year_month column to improve performance.

There is a very good whitepaper on table partitioning written by Kimberley Tripp: http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
lonely consultant
Sunday, August 12, 2007
 
 
75000 rows per month is a tiny amount of data for a modern RDBMS like SQL Server 2005 to handle. Don't really need any optimisation for that trivial amount of data, apart from selecting suitable indexes/clustering.

As an aside, I used to do telecoms carrier billing databases, so 100 million rows a month was more the norm. Even then, it was no problem handling that many. I remember when the database went over the billion row mark. That was quite a landmark. End of aside.
Entries of Confusion Send private email
Sunday, August 12, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz