Jump to content
Sign in to follow this  
gmc1103

Database implementation suggestion

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

Share this post


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.

Share this post


Link to post
Share on other sites

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

Share this post


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.

Share this post


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

Share this post


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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Sign in to follow this  

×
×
  • 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.