Skip to content
Advertisement

SQL: database design [closed]

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?

enter image description here

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement