Jump to content

Database implementation suggestion


Go to solution Solved by requinix,

Recommended Posts

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

 

post-174418-0-25065500-1486633485_thumb.png

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

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

Link to post
Share on other sites

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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.