Mysql – Design advice regarding a users/groups/tasks database scheme

database-designdatabase-recommendationMySQL

Here are my requirements:

  • one user can have many tasks
  • one group can have many task
  • one group can have many users
  • only invited user by a user can be added into a group

Does this db design appear to model these requirements?

enter image description here

In group table, group_id should be the FK of which? there are 2 table linked to that table. 1 FK to 2 PK? possible?

Best Answer

Try these design which will satisfy your requirements.

Group

 - Group Id Primary Key
 - Group Name)

User

- User Id Primary Key
- Name
- Email 
- Created on 
- Group Id references Group(Group Id), Referred User ID references User(User ID))

Task

- Task ID Primary Key 
- Name 
- Priority 
- Date)

User-Tasks

- Relation ID Primary Key 
- User ID references User(User ID)
- Task ID references Task(Task ID))
Related Question