Database implementation suggestion

[gmc1103]

Hi

 

I'm still developing my database and now i have another task

 

So in my school i have teachers, table Utilizador

id_utilizador(teacher)

 

The table visitas have the following data.

• id;
• classroom_id;
• place_visit;
• date_leaving;
• hour_leaving;
• date_arriving;
• hour_arriving;
• subjects(teachers subjects);
• teachers;
• objectives;
• transportation;
• price;
• students(how many);
• itinerary;
• observations;
• id_year (school year;
• registered (date)

Classroom table turmas

• id_turma(classroom_id);
• classroom_name
• active(yes/no)

My related table rel_visitas_utilizador

• id_visita(foreign key);
• id_utilizador(foreign key)
• done(yes/no)
• valid(yes/no)
• file(pdf generated and saved in webserver path)

So right now this is how this work, teh process

A teacher must can schedule a study visit, when he fill the form, all the data from form are sent the the manager and he must define if this visit is valid or no (rel_visitas_utilizador).

So with the following explanation and the image provided can someone give me some suggestions to improve this database?

 

Any questions, just ask

 

[requinix]

What you described sounds like the teacher creates a study visit (new row in visitas) for every time they want a study visit. This one visitas row then gets a rel_visitas_utilizador row for approval by a manager.

 

Does that mean there is a one-to-one relationship between visitas and rel_visitas_utilizador? One row in each table matches up with only one row in the other.

[gmc1103]

Hi Requinix

 

Right now is what i see in the database. For me it's sound a little confusing. I understand the schema, but regarding the point you mention it seems it's 1_N from visitas to rel_visitas_utilizador..

[requinix]

Right, but if your question is what to do regarding the schema, then first you need to understand the application. I see that it's 1-N now but I'm wondering whether that is the correct relationship.

[gmc1103]

I understand the application, 

When the user schedule the study visit, the data is inserted into the visitas table, then the id generated in the table visitas is inserted into the rel_visitas_utilizador. My question was if that implementation (all tables are correct or can they can be improved).

The table rel_visitas_utilizador is where is keeped some extra informations about that visit (like pdf file, validate or not, realizado(made or not), etc). About the 1-N, I doubt since rel_visitas_utilizador should only keep one id, can't be repeated

[requinix]

Okay, so it's a 1-1 relationship then. Since there are only a couple columns in that table, and you know that those columns will have values, I would move them into the visitas table. It's not an important change to make, but it does reduce complexity.

 

And I think that is the only change I would make.

[gmc1103]

Thanks

But i keep the rel_visitas_utilizador  table? with id_visita and id_user?

[requinix]

If you moved done, valid, and file into the visitas table then rel_visitas_utilizador would not be needed anymore.

[gmc1103]

Thank you.