Gopal Arora Gopal Arora - 1 year ago 65
SQL Question

Design of table structure in oracle database

I have a requirement which is to keep the data of 2000 employees attendance.

So the attendance table should have design?

365 columns of each day in a year, 2000 pre-inserted records

And i will update it daily via java program


5 columns 2000 records daily insert for 365 days?

Or is there any better approach?


I think you're over-thinking things. Assuming you already have an employee table with the employees' details, all you really need is a another table with the employee's ID and the date (and possibly entrance and exit times, if you care about it), where the presence of a row indicates attendance on that date and the lack of if indicates lack of attendance. You can then find this absence days by left joining on the employee table.

CREATE TABLE employee (
    -- other details...

CREATE TABLE attendance (
    employee_id NOT NULL REFERENCES employee(id),
    attendance_date DATE NOT NULL,
    PRIMARY_KEY(employee_id, attendance_date)