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);
