I'm creating a web platform on AWS to enable restaurant owners in my community to create menus. I'm moving from a relational database model to a NoSQL solution and wondering the best way to organize this data. My current relational model is as follows:
Table 'restaurants': id (int / primary key), name, owner (int)
Table 'categories': id (int / primary key), restaurant (int), parent (int)
Table 'items': id (primary key), name, category (int)
Only the owner should be allowed to create/update/delete places, categories, and items.
What would you recommend as a de-normalized solution given the ownership constraint? I was thinking of doing the following:
Table 'restaurants': id (primary key), owner (sort key), categories (list of ids)
Table 'categories': id (primary key), restaurant (id), items (list of item objects), subcategories (list of category ids)
Wondering if it'd be better to have all category data contained within the restaurant table. As an example, a user should only be able to add an item to a category if they are the owner of the associated restaurant, which would take an additional query, per above.
Depends mostly how you use your data . If usually the Restaurant is read full, is ok to have all in the restaurants table.
If you have a lot of operations only on one category , for example many are interested only in food and not interested in drinks , then it would be good to have this done on categories.
I think for some restaurants would be better to have it split in categories and keep common data on restaurant level , address, phone , opening hours and so on .
I don't think write is important , seems to be over 90% read web site.0
Perhaps a cache solution ? Redis ? Memcache ? this would speed up even more.