CODEWITHSUNDEEP
ruby-on-railsdatabase-design
Squadrons
Squadrons

Reputation: 2557

Database schema - polymorphic association vs separate tables

Users have many tasks. (tasks.user_id = user_id)

tasks have many tags (implemented through Acts as taggable on

Users have many lists -> which have many tasks (A list is just a wrapper for multiple tags, for example {List id => 1, name => "shopping vacation summer"} will retrieve all tasks tagged with all of those tags)

If, for example, a user goes to the url tags/shopping%20vacation, my code would do a lookup for:

Task.all.tagged_with(["shopping", "vacation"])

There is no Lookup table or foreign key for task.list_id

I want users to be able to share tasks, lists, and tags with other users.

A user sharing a task shares just that task with another user.

A user sharing a list opens up all the tasks in that list to another user.

A user sharing a tag opens all of that users tasks tagged with that tag to another user.

To me, there are a few options, and I'd like some input on the pluses and minuses of each that I may not have thought about.

One is a distinct table for each shareable item:

shared_tasks: task_id, shared_to_id(or user_id) shared_lists: list_id, shared_to_id shared_tags: tag_id, shared_to_id

OR: a polymorphic association with

shareables: shared_id(item_to_share), shared_to_id, shared_type

What are the pluses and minuses of each?

Is there another solution that I haven't thought of?

Upvotes: 1

Views: 480

Answers (1)

Young Bob
Young Bob

Reputation: 743

Personally I would change the schema design. Taking Tasks as an example i would now treat it as a many-to-many relationship with User instead of many-to-1 as it is currently. So this would mean removing column tasks.UserId and adding a new table called UserTasks which links Tasks with Users defined as (UserId, TaskId, IsOwner). IsOwner is set to 1 for the user who the task belongs to and 0 if it is shared but belongs to someone else. This will make queries easier for example you could do do a single select to get both owned and shared tasks rather than 2 separate queries and also avoid union's.

Upvotes: 2

Related Questions