Relational database design cycle

Question

I have the following database design:

I want to add a new table named Task, which would have a one-to-many relationship with a Project(Project will have one or more tasks and particular task will belong to only one project). Next users will also be assigned to different tasks in a project(basically User table needs another many to many relationship with a Task, but that creates a loop in the design). Is this a good practice or should I avoid making loops in a design?

Laporte

Answer 1

Next users will also be assigned to different tasks in a project(basically User table needs another many to many relationship with a Task...)

Probably not.

You probably need only those users who are related to a particular project assigned to specific tasks. So you need a foreign key from tasks to reference the project's users, which seems to be the oddly named table "Project_userprojectrelation".

"Loops" are a nuisance sometimes when you need to cascade updates or deletes. But you can usually delete rows from the "end" or the "middle" of the loop toward the "top". The only time that doesn't work is when you have foreign keys chasing each other around the loop in the same direction. Most dbms's will warn you about that kind of problem.

Housing