CODEWITHSUNDEEP
ruby-on-railsdatabase-design
Josh Brody
Josh Brody

Reputation: 5363

Database design for categorized to-do app

I'm trying to figure out what the best (most logical) way of designing this database for an app that will allow a user to CRUD to-do tasks (more or less), however, they're organized into hard-coded categories.

So let's say you're about to go to your favorite department store. You need to hit up the Women's floor and pick up your girlfriend the shoes she ordered, and the matching dress (which is on the completely other side of the store, but on the same floor.) Then, you need to go to the Boy's department for your little brother, and pick up two different pairs of shorts, one pair of pants, and a new pair of shoes.

The Women's Floor and Boy's Department are two examples of categories that the shopping list items will fall into.

So it looks like this:

* Women's Floor
     1 Pair Shoes
     1 Dress

* Boy's Department
     2 Shorts
     1 Pant
     1 Pair Shoes

So my database design could look like so...

Categories: id, title
ListIndex: id, user_id
ShoppingList: id, listindex_id, category_id, item_id, order, active
Items: id, name, category_id

Categories would be Boy's Department, Women's Floor, etc. Users would not be able to create new categories, but instead, we would predefine the categories

ListIndex would provide a master relation to the shopping list as a whole.

ShoppingList would be the actual shopping list (active would be 0/1, so the user could have a way to remind themselves that they bought the item / put it in their cart.)

Items would have a list of items that are available to put into the to-do tasks. We would categorize these ourselves on the back-end.

Is this the right way of doing it?

Upvotes: 2

Views: 579

Answers (1)

Mario Zigliotto
Mario Zigliotto

Reputation: 9025

Hopefully i understood the description of the problem correctly but i was thinking here's one way you could lay your database and models out. I also don't think you need the ListIndex:

Database Tables

Categories: id, title

ShoppingLists: id, user_id, order, active

Items: id, title

ShoppingListItems: id, item_id, shopping_list_id, quantity

CategorizedItems: id, category_id, item_id

Users: id, name

Models

User:
has_many shopping_lists

ShoppingList:
belongs_to user
has_many shopping_list_items
has_many items, through shopping_list_items

Items:
has_many categorized_items
has_many categories, through categorized_items
(optional: you could query an item for the shopping lists that it is on)
has_many shopping_list_items
has_many shopping_lists, through shopping_list_items

Categories:
has_many categorized_items
has_many items, through categorized_items

My thinking is this --

Individual categories are basically static, that's pretty straight forward.
Shopping lists represent a User's (via the user_id) list of items that will be purchased. The link between an item and a shopping list could take place in a join table called ShoppingListItems where each row links together the relationship between a list, an item, and the quantity.

Items are interesting because in your example an item is something that can actually be in multiple categories. i.e. "pants" can be in boys/girls/men/women and probably pets :(. To support that I think you can use another join table called CategorizedItems that basically lets you query for "items in a particular category" or "categories an item is in".

Upvotes: 4

Related Questions