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
Sunday, August 12, 2007

Deleting …Approving …

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.

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.