2

I am pretty new to databases and SQL. Have following question, didn't figured out how to google this.

I am designing database structure for my application. Let's say I have following tables:

user

  • id (PK)
  • department_id (FK)
  • first_name
  • last_name

department

  • id (PK)
  • name

task

  • id (PK)
  • user_id (FK)
  • ? department_id (FK)

Should I add department_id into task, or maybe user_id will be sufficient? In case if I want to figure out user's department while selecting data from tasks, how do I build the query for it?

UPD:

Let's assume we don't plan to change task's owner through time. I consider extra foreign keys only as shortucts to avoid extra joins while selecting data.

Example: let's assume we have one more table:

session

  • ID (PK)
  • started_time
  • task_id (FK)
  • ? user_id (FK)

If I won't add user_id, I will need to perform two extra join, first to get user_id from task_id and then, to get department_id from user_id.

The question is - is this generally normal or bad practice?

  • If a user changes department, all his associated tasks also change their department? If so, you can omit the department FK on Task. – EzLo Jul 3 '18 at 11:39
2

In case if I want to figure out user's department while selecting data from tasks, how do I build the query for it?

You'd first join user and then department.

SELECT *
       FROM task
            INNER JOIN user
                       ON user.id = task_user.id
            INNER JOIN department
                       ON department.id = user.department_id;

Such "chains" of joins are no problem for a DBMS in general, it's built to do that (but you should consider proper indexing to support it (but that's also true for queries not using joins)). The "shortcuts" you have in mind on the other hand bring a certain level of denormalization and with that a handful of problems.

Whether to include department_id in tasks depends on what you're trying to model.

If a task can be assigned to a whole department without a specific user from that department, you'll need it. Add a check constraint enforcing that one of both is null, to be able to distinguish between task assigned to only a user and those only assigned to a department.

If only users can have tasks assigned, you won't need it. You can query the department as shown above. Further more in that case it's dangerous to include it or at least it makes things more complicated. You have to check then, that the integrity isn't violated. Say if a user assigned to a task is in department A but the department assigned to the task is B. That obviously doesn't match. Unless users can be in several departments an a task should be assigned to a user in the role as a member of a specific department. That's one of the problems of denormalization I mentioned above.

(You see, there are many ifs here, you need to think through. Maybe even more than I just came up with!)

If you don't need the department_id because of the logic, I'd recommend not to use it in task. It most likely brings more problems and doesn't even solve another as joining usually is no problem.

  • great answer, please see updated post – JackHammer Jul 3 '18 at 12:22
  • @JackHammer: I updated the answer to address your concerns about joining. – sticky bit Jul 3 '18 at 12:59

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.