Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 7 down vote accepted

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);
share|improve this answer
    
Here, How do I differentiate the phases for a particular project. For ex: if user john spend 3 hours in a project on SRS phase and also he spent 2 hours for another project on pseudo coding phase. So, how do I differentiate that two for requirement like "Find the total number of hours used by john for project A on SRS phase." – mrg Jan 31 at 19:53
    
You can create an additional table if the phases of the project are strict (eg they have a specific order or particular legally-significant labels, and you want to enforce that) or you can simply add a free-form text column in the activity_logs table that can be filled in however is relevant. It depends on your application. – Chris Harrington Jan 31 at 19:57
    
Ok. Thankyou so much for your help...... – mrg Jan 31 at 19:58
    
supervisor_employee_id is also a foreign key to employees, and may be better in projects. – Michael Green Jan 31 at 20:57
    
@MichaelGreen Correct, thanks for pointing out the missing FK. Of course, depending on how you define "supervisor", you may be right. My real-life work, on the other hand, uses a hierarchy of supervisors per project, and my original thought in including it was to FK back to project_memberships itself. I didn't really flesh it out all the way, it was a quick jot to get a concept across. – Chris Harrington Jan 31 at 21:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.