Postgresql – How to design a database

database-designpostgresql

The file which contains the day to day activities of each employee. And it shows the detailed description of employee what they done at work time. So these files are prepared by employee.

Now, my requirement is how to design a database for this model. I know only the basics of databases and yet I didn't design any databases. So any one help me to solve how to create a database design for the application.

The design should also work for the query to get details about individual employee like

For Ex:

  1. Total time spent by the shiva 'shiva' on CCTNS project

  2. To find the team leader for the specified project

  3. To find the members in a particular project

Best Answer

I would suggest two fundamental tables for this purpose, if you're creating this all from scratch. This assumes that this is being created in PostgreSQL.

An employee table containing their name and whatever other details are important for your application, with a numeric primary key. The primary key is important because you may have more than one employee with the same name, and you may have employees that change names in the future. Using a key allows the information about their time to remain consistent.

An activity log table containing:

  • a primary key column (type SERIAL is likely fine for this)
  • an employee ID column (this will reference the column in the other table)
  • a project column (you can make this free-text, or create another table with a primary key and reference it here), this column should be nullable to reflect activity log entries that aren't related to a project (eg tea break)
  • a tstzrange column to represent the period for the log entry

Now this schema doesn't contain enough information to identify a team leader. You could add this information to another table, referencing your employee (and possibly project) table.

Here's a quick example schema you can play with:

 CREATE TABLE employees (
     employee_id SERIAL NOT NULL,
     employee_name text NOT NULL,
     PRIMARY KEY (employee_id)
 );

 CREATE TABLE projects (
     project_id SERIAL NOT NULL,
     project_name text NOT NULL,
     PRIMARY KEY (project_id)
 );

 CREATE TABLE project_memberships (
     employee_id integer NOT NULL REFERENCES employees,
     project_id integer NOT NULL REFERENCES projects,
     supervisor_employee_id integer REFERENCES employees,
     PRIMARY KEY (employee_id, project_id)
 );

 CREATE TABLE activity_logs (
     activity_log_id SERIAL NOT NULL,
     employee_id integer NOT NULL REFERENCES employees,
     project_id integer,
     activity_time tstzrange NOT NULL,
     activity_notes text,
     PRIMARY KEY (activity_log_id)
 );
 ALTER TABLE ONLY activity_logs
     ADD CONSTRAINT activity_logs_project_member_fkey FOREIGN KEY (employee_id, project_id) REFERENCES project_memberships(employee_id, project_id);
Related Question