I need some advice on designing a sql database schema. I get an excel sheet every month in the following structure. I would like some advice on how I can implement the table structure keeping in mind that they want to report on it
I thought I could create two tables one for tasktypes[Safety, security, identifiers etc..], and the other for departments[IT, Kitchen, Training etc..]. But how can I get the percentages per months? Is it best to create another table with the results and have a column for months in it?
Advertisement
Answer
Basically the structure it should be something like this.
Create Table TaskType( id serial primary key not null, name varchar(30)not null ); Create Table Department( id serial primary key not null, name varchar(30)not null );
and finally on the assignation.
Create Table Assignment( id serial primary key not null, id_department int not null, id_task int not null, percentageValue numeric(4,2) not null, schedule timestamp not null, foreign key(id_department) references Department(id), foreign key(id_task) references TaskType(id) );
To handle the date you have to create a column named schedule and the data type is a timestamp. and then filter by month.