0

Suppose, I have three tables named Table user, team, task

user
user_id
team_id
team
team_id
task
task_id
user_id
team_id

where the User table references team_id then a task table references the user_id and team_id. It sorts of create an indirect duplication of team_id because it was already referenced by user_id.

  1. Is there a name for this?
  2. Is this bad practice? (I have an inkling I should not do this)
  3. Possible workarounds?

Thank you!

Garri Sumalapao Farol
  • 3
  • 2
  • 1
    Can a user only belong to one team? If not, having say `team`, `user` and `team_user` tables might make sense. If a task can be allocated to both a team and a member of that team then having both of those in the task table doesn't seem unreasonable? – Alex Poole May 10 '21 at 14:16
  • Thank you for answering. Yes, the relationship is one(team) to many(user) for user and team then one(user) to many(task) and one(team) to many(task). Sorry if my reply is a bit verbose, I am still not well-versed with rdbms – Garri Sumalapao Farol May 10 '21 at 14:26

1 Answers1

0

well it depends :

  • Could a task be assigned to a team and someone from another team do it?
    if yes then team_ id should be referenced to team table
  • Could a user id be repeated inside each team ? if yes then you need a combined fk of (user_id,team_id) referenced user( yuser_id, team_id) , here no link to team table.
  • if the answer is no to above questions, then team_id is redundant and cause data inconsistency
eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • 1. No, only one team can do a specific task. 2. No, one user can only have one team while the team can have many users 3. Should I just use nested join to select the team_id via user_id? Thank you for answering! :) – Garri Sumalapao Farol May 10 '21 at 14:27