Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rpk2006
Starting Member
1 Post |
 Posted - 2007-08-12 : 12:24:03
|
| 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 100Jan-2007 101 VEHICLE A 200Jan-2007 101 RECOV. D 5000As 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) Any other SQL Server 2005 feature to use while fetching records from this table. |
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-12 : 17:13:53
|
| 1) Irrespective of performance it looks like you should conbert DeductionMaster and AllowanceMaster into a single table. If certain payments can only be paid or only be deducted then you need to set a further table. There is little point having a deduction\allowance ID if it is not used on the foreign key to Emp_Pay_Details. Either cascade the natural key and get rid of the surrogate or cascade the surrogate.2) Sounds reasonable UNLESS an employee can have both a payment and a deduction in the same month for a particular payment\ dedution type. Similarly, if they can have 2+ payments or deductions per month for the same type. You would need to decide what is the best order for the index in relation to the inserts & selects. You would also need to decide if this is best clustered or non-clustered. |
 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-08-28 : 10:36:30
|
| I'm presuming that you're going to want to aggregate this information, in which case storing negative numbers for either deductions or allowances (depending upon your accounting perspective) is going to make this much easier, and tells you immediately which category a particular value falls into, without the need for a separate identifier. However, it really depends upon the type of analysis that is likely to be required.You definitely don't want to be storing a 30 byte varchar (TRAN_DETAIL) in this table if you can help it. As pootle_flump suggests, combine the Allowance and Deduction tables into one and add a foreign key reference to this in the Emp_Pay_Details table. While we're on the subject, you probably want to have a think about data types in general. I.e. do you really need precision of 20 for [Amount]? Even if you're storing amounts in Rupees, these will still be numbers of the order of magnitude required to store GDP rather than payroll figures. The aim is to get this table as narrow as possible (in terms of bytes per row).In terms of other SQL Server 2005 features, I'd normally suggest partitioning. However, the volumes you're looking at are pretty tiny, so you wouldn't derive any benefit, and would just be introducing an administrative burden.Mark |
|
|
|
|
|
|
|
|
Optimizing this table design