Modelling a shared entity puzzle

Question

Have the following entities in my domain:

Project
Task
User
Context

A user can have multiple projects, tasks and contexts. A project can have tasks and contexts and a task can have contexts.

I am a little unsure as how best to model Contexts as it will belong to multiple entities i.e. a user can add a list of contexts, these contexts will be associated with te user. A user can then add a project and select from the list of user contexts,contexts to add to the Project effectively making project contexts.

Any help would be appreciated, thansk in advance...

Answer 1

Depends a little on the purpose of the Context table. Can you have a context that is created be shared among the User, Task, or Projects? If so then a Many-to-Many relationship table may help out for each of the different joins. So a UserContext, ProjectContext, TaskContext table. They would just house the primary keys of the two tables and maybe a timestamp, up to you.

If you do not share any of the content with the other tables such that each link is unique to the person creating it you may want to just add a context type to the mix. So a ContextType table would be added to the mix. You would still want a relationship table such that a person could have multiple contexts, etc. So then then you could have a ContextRelationshipTable that links the foreign key from the parent table to the context table and you could use the type as a filter.

However I'd probably just use the Many-To-Many approach and just have the link table(s).