Home>

I created a program that uses SQL (database).
When trying to normalize the relation schema after completion,
It will be different from the finished one, but if it is different,
It will be difficult to manage, so under what conditions I created the relation schema
I want to know.

Initial schema

A (employee number, name, department number, department name, password,working hours number, attendance time, work hours)
* Bold is the primary key

Created program schema

A (Employee number, name, department number, password)
B (department number, department name)
C (working hours number, employee number, attendance time, leave time)

Condition

Employee number The same number does not exist.
Name The employee does not have the same name and department number.
Department number Employees with the same department number have the same department name.
Password The same password may exist multiple times.
Working hours number The same number does not exist.
Working hours There may be multiple employees with the same time.
Work hours There may be multiple employees with the same time.

If the conditions are met

A (Employee number, name, department number, department name, working hours number)
B (department number, department name)
C (working hours number, attendance hours)
D (working hours number, work hours)

About the program

I wanted to create a time clock to manage working hours.
A working time number is required to identify, delete or update commands.
In the schema of the created program, A and C are combined with employee numbers, so employee numbers are necessary for both, but if you disassemble them, attendance/retirement hours and employee numbers will not be combined.

Environment

Ubuntu, PostgreSQL

  • Answer # 1

      

    Attempting to normalize the relation schema

    Is it done with tools or something?

    I'm not sure, but it's because you are using a surrogate key called "working hours number".

    Do you use natural keys?
    C (employee number,attempt time, leave time)

    How about making working hours numbers subordinate to employee numbers?
    C (employee number,working hours number, attendance time, leaving time)

  • Answer # 2

    employee master: employee number, name, department number (PRIMARY KEY employee number)
    Department Master: Department Number, Department Name (PRIMARY KEY Department Number)
    Attendance table: employee number, attendance date, attendance date (PRIMARY KEY employee number, attendance date)

    How about

    ?