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.
x
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.