Member Avatar for simransuri

hey!
i need to create a customised table "Employee" in which i have to insert employeeid,employeename and salary...on inserting the salary,the table should automatically calculate Hra(10% of basic salary),DA(9% of basic salary) and hence calculate net salary...so...plz guide me...how to do this?

  • 3 Contributors
  • 6 Replies
  • 116 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by smantscheff

Dani AI

Generated

Short answer: avoid storing derived fields unless you need them materialized for indexing or legacy compatibility. 's suggestion to use a view is the simplest and safest; sketched the arithmetic you need. Below are practical options, pros/cons, and small examples you can apply.

A view (recommended when derived values change with base data) keeps the table normalized and guarantees consistency. Create a view that selects the base columns and adds arithmetic expressions for the allowances and net pay. Pros: no extra storage, no update logic. Cons: computed on read (might affect performance on large scans) and you cannot index expressions inside a plain view. See MySQL docs for syntax: CREATE VIEW.

If you need the values stored or indexed, use MySQL generated columns (5.7+). Use DECIMAL for money, and decide between VIRTUAL (no storage) and STORED (materialized, indexable). Example:

CREATE TABLE Employee (
  employeeid INT PRIMARY KEY AUTO_INCREMENT,
  employeename VARCHAR(100),
  salary DECIMAL(12,2) NOT NULL,
  hra   DECIMAL(12,2) GENERATED ALWAYS AS (salary * 0.10) STORED,
  da    DECIMAL(12,2) GENERATED ALWAYS AS (salary * 0.09) STORED,
  net   DECIMAL(12,2) GENERATED ALWAYS AS (salary + hra + da) STORED
);

If you run an older MySQL, use BEFORE INSERT and BEFORE UPDATE triggers to populate fields, and be careful to implement both triggers so updates to salary stay consistent. Always ROUND or CAST to the desired scale, test INSERT/UPDATE paths, and consider transaction behavior. See MySQL docs: Generated Columns and Triggers.

Recommended Answers

All 6 Replies

Member Avatar for debasisdas

so, what you have done so far ?

Member Avatar for smantscheff

how to do this

Don't. Do not store calculated values in your database. If Hra and DA are percentages of another database field, create a view which calculates them instead of storing them.

Member Avatar for simransuri

but how can i calculate values in a view???
can u plz show me thru some example...plz...i need to complete this by today otherwise my teacher will screw me...plz guide me

Member Avatar for debasisdas

You need to crate a view and add the calculated values as columns.

HRA = salary * 10/100
DA = salary * 9 /100
net = HRA + DA

Edited by debasisdas because: n/a
Member Avatar for simransuri

i still didnt get u...need an example...plz read my above query carefully

commented: you need to read carefully, don't expext to be spoon feed here. -2
Member Avatar for smantscheff 
create view xyz as 
select *, salary * 10 / 100 as HRA, salary * 9/100 as DA, salary * 19/100 as net from employees
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.