Mysql – Database Design Review

database-designMySQL

I´m currently developing a vacationplaner and I´m facing some problems during the database design stage.

The Requirements

The User has a specified amount of Vacation Days. Therefore I have to keep track of how much days he has left (tbl vacationUser & userAccount).

The User can create a request, which needs to be confirmed by another user that is specified as Admin (tbl group). While the Request is unconfirmed, the unconfirmed days are stored (to prevent the user to make too much requests) in the tbl userAccount. After a user from group Admin has confirmed that, the unconfirmed day´s are subtracted from daysLeft.

In addition to the specified group of the User (Admin/ User) EACH User is part of a Department. Each Department has a MinStock, if that is reached, no user of this department can take vacation on these days.

For EACH department exists a User that is responsible for that department, and ONE user who is the "deputy" of that responsible user. If the responsible User HAS vacation, the deputy CANNOT take vacation on these days.

So that is the short version of what I´m trying to do.

The Problem

Database Design

This database design was made with use of the junction tableconcept. The data are mostly normalized and summarized in one table users.

My thoughts on this Design

I thought it would enable me a good way to get all important data, by accessing the Userstable. If a User is a Responsible or Deputy of a department, there would be an ID otherwise it would be null.

Here is my Question – a bit hidden under the "giving you information part"

As I stared to think about this design, i don´t liked that I have two tables that just have an ID responsible & deputy table.
The responsible & deputy of a department are on the one hand Departmentrelated information, but on the other hand userrelated information. By using the junction table users this information are connected, but is it the right way I did that? Or should responsible & deputy just be related to the Department table?

Is it better to connect the userAccount & vacationGroupdirectly with the vacationUser table by setting the userID in those tables and add an FK?
The same for the department and the `responsible & deputy (my thought´s on this are mentioned above).

I would wish to discuss this design and would like to know if I will run into any problems here.

Any help is welcomed!

If you need some further Information, leave a comment!

Use of Junction table

Edit:
Here is the entire SQL code, to play with the database SQL

EDIT 2:

I´ve recognized that the current design does not work, because I will never be able to associate a User with a userAccount, because there must an existing UserAccount, before I can fill the table Users.
So probably my initial thoughts were correct, that I have to connect the userAccount directly with the vacationUser table via the userID.

But when I do this, from the logical part I should also connect the group directly with the user. I would have to add the Column groupID to the user´s table to associated both. Is that correct?

Does it even make sense to use the table users?

Here is a visualization of some of my Edit 2 thoughts, combined with some Ideas from my Initial Concept.
With this design I´ll probably not have the troubles about the Useraccount (EDIT 2), but will keep the easy accessibility to verify if a User is responsible or deputyof a Department. But I´m still unsure if I shouldn´t do it completely the way as I mentioned in EDIT 2. What do you guys think?

SQL 2

I hope someone will still help/ answer me, even if this Question is going to be bigger and bigger. Therefore I will stop here and wait for your ideas 🙂

Design 2

Best Answer

First identify the entities in your design (in hierarchical order, i.e. in dependency):

  • Department
  • User
  • VacationRequest

Then identify the relationships:

  • Each Department have one or more User
  • Each User can have 0 or more VacationRequest

Then the business rules:

  • Each Department must have one responsible and one deputy.

  • If the responsible of the department have a confirmed vacation then in the same period then the deputy can't have a confirmed vacation (and viceversa)

How the rules could be implemented:

  • Create the tables Department, User, VacationRequest.
  • In the user table assign the user as responsible of his own department or deputy.
  • Each time you enter a new VacationRequest asign it with the Confirmed field on false.
  • Each time you confirm a Vacation via a stored procedure you must check if the vacations not colide with the deputy or viceversa (also you must check if there is a deputy effectively)

I hope it would help you.

Related Question