I´m currently developing a vacationplaner and I´m facing some problems during the database design stage.
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.
This database design was made with use of the
junction tableconcept. The data are mostly normalized and summarized in one table
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
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.
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
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!
Edit: Here is the entire SQL code, to play with the database SQL
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
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?
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 :)