2

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

migrated from stackoverflow.com Aug 10 '16 at 13:31

This question came from our site for professional and enthusiast programmers.

  • Agree that was harsh! I've upvoted it as I think it's fine as a question – Simon Woolf Aug 10 '16 at 13:01
  • Can you produce the text 'creates' for this? – Drew Aug 10 '16 at 13:06
  • You mean the SQL code? Or what do you mean by "text 'creates'"? – el solo lobo Aug 10 '16 at 13:08
  • There are a number of things that could be improved/optimised here. I don't think responsibleOfDepartment or deputyOfDepartment add any value. If I get time later I will post a solution, however I expect that someone else will be quicker . . . – Simon Woolf Aug 10 '16 at 13:16
  • I would love to hear the things that can be improved. We will see, if I get any answers ;) Thanks in advance – el solo lobo Aug 10 '16 at 13:20
1

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.

  • How would you assign a user as responsible? Would you just make a new column (null/ not null) to see if he is responsible or would you create a new table? – el solo lobo Aug 11 '16 at 14:32
  • Look I was thinking in your previous commentary, if we made an additional table which makes the relationship between a user and a department, what would be the key of that table? Obviously is not departmentId, UserId because a User belongs to one department – rlartiga Aug 11 '16 at 14:35
  • so you would do an additional table? Currently I´m testing it with just two new columns which are either null or 1 within the user table. But I´m not sure if I´m happy with that, but it currently works. What do you think? – el solo lobo Aug 11 '16 at 14:55
  • But why not use codes? R (responsible),D (Deputy) N (None). No I don't think it would make a improvement just by adding the tables. Because if you made a table between department and user it must contain all the users. If you don't do so you are not going to know in which department an user is. – rlartiga Aug 11 '16 at 14:57
  • I like your Idea with the Codes. Have done it like you said and it seems to work. Thanks for help buddy! – el solo lobo Aug 12 '16 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.