You are not logged in.

Database Normalisation ??

sunil_thaha

Intermediate

  • "sunil_thaha" is male
  • "sunil_thaha" started this thread

Posts: 484

  • Send private message

1

Wednesday, December 22nd 2004, 2:22pm

Database Normalisation ??

Hi Guys
Here goes my doubt

I am doing a Payroll now

There is something called PF
Each Employee must have a minimum PF of 12 %

and if an Employee wishes He can contibute more

So there is a PF Table where the minimum PF is Kept

and Now my Doubt is
if I should add a Extra PF % to the Employee Table

the Table then will be

Source code

 
1
2
3

|----------+-------------------------+-------------------------|
| (pkey)  | Employee Details |  Extra Contibution | 
|----------+-------------------------+-------------------------|

or Create a Employee-PF Table where only those Employees who Intend to contribute more will be Kept

So the Table will be Like this

Source code

 
1
2
3

|--------+------------------------+-------------------------|
| pkey  |     Employee_ID    | ExtraContibution  | 
|--------+------------------------+-------------------------|



Please give your valuable Suggestions

Please Help &
And I would like to thank U in advance
Check this 4m out http://QtCentre.Org - The Qt Community Centre

wysota

Sage

  • "wysota" is male

Posts: 4,276

Location: Warsaw, POLAND

  • Send private message

2

Wednesday, December 22nd 2004, 3:21pm

RE: Database Normalisation ??

If every emploee must have this data associated with him/her, then the first is better, because you don't have to do JOINs to fetch the data. There is no use making a table that holds one value (I think it's 'Extra Contribution' here).
QtCentre.org - the largest Qt community site and forum where questions get answered

djanubis

Professional

  • "djanubis" is male

Posts: 1,370

Location: Moulins France

Occupation: Software ingeneering

  • Send private message

3

Wednesday, December 22nd 2004, 4:00pm

RE: Database Normalisation ??

I agree with wysota. Creating a table for only one optional field will force you to use JOIN on tables for very little interest.

When it comes to payroll, depending on each country laws, will often make us use a different strategy.
As a real example, in France we have very complex payroll management laws, with a huge list of specific items by profession and employee class.
So, I use payroll templates for each and every category.

Employee nominative information is gathered in a main table.
All financial and fiscal data is in another table

emp_id
entry_pos
entry_label
base_value
emp_base_rate
emp_base_amount
comp_base_rate
comp_base_amount

This allows flexible payroll management with easy template update when law changes (about twice a year!).
But a simpler model is possible if you live in a country where things are stable.
Never patch not working code. Rewrite it !
Never patch badly designed classes. Recreate them cleanly.
(Excerpts from Computing Bible)

Home of the Lab project

sunil_thaha

Intermediate

  • "sunil_thaha" is male
  • "sunil_thaha" started this thread

Posts: 484

  • Send private message

4

Thursday, December 23rd 2004, 4:35am

Thanks for the Help

I will tell u what the real Situation is

A PF has the following Parts

CPF, EPF , Pesion

CPF - is Fixed by the govt to be 12 % of ( Basic Pay + DA )
and the Employees may contribute more

Now as for EPF and Pension
there is a sealing Amt now it is 6500

Source code

 
1
2
3
4
5
6

 Gross =  BasicPay + DA

if  ( BasicPay + DA ) > 6500 then      Gross = Sealing Amt;

EPF = ( 8.33% again fixed by govt)  of Gross 
Pension =  ( 3.67% again fixed by govt)  of Gross


so I made a PF Table as Follows
+--------+----------+----------------+-----------------------+
|CPF % | EPF % | Pension % | EPF Limiting Amt |
+--------+----------+----------------+-----------------------+

In the Above table I will store the details of the PF as per the Govt's Norms

Now for CPF An Employee can contibute more ( As per his wish )

So Now the Question Repeats
if I should Add a Field to the Emp Table

Employee Table
|----------+-------------------------+-------------------------|
| (pkey) | Employee Details | Extra Contibution |
|----------+-------------------------+-------------------------|


or Create a Employee-PF Table where only those Employees who Intend to contribute more will be Kept

Employee-PF Table
|--------+------------------------+-------------------------|
| pkey | Employee_ID | ExtraContibution |
|--------+------------------------+-------------------------|

Which is better Normailsed ?
And Which Should i Adopt ?
Check this 4m out http://QtCentre.Org - The Qt Community Centre

djanubis

Professional

  • "djanubis" is male

Posts: 1,370

Location: Moulins France

Occupation: Software ingeneering

  • Send private message

5

Thursday, December 23rd 2004, 6:53am

As this is a single field, keep it with common data in the same table, as already told.
If you had a family of optional fields, the second approach would be better.

As a common rule, always think in terms of balance when dealing with database design.
Creating a table with two mandatory fields: Primary Key and reference to store only a value gives a 0.5 balance wich is bad.
Creating a table with PK, Reference and 3 or more fields is good as you get a positive balance.

And always think of modern databases wich support CLOB (called TEXT in MySQL) and BLOB when you deal with tabular variant data.

There are numeroius resources on the net about database design. A good start is Introduction to Relational Database Design wich points out some issues when you have JOINs, namely Referential Integrity. The more joins, the more Referential Integrity problems.
Never patch not working code. Rewrite it !
Never patch badly designed classes. Recreate them cleanly.
(Excerpts from Computing Bible)

Home of the Lab project