Hey folks, I'm currently working on a relational schema for restaurant menus and I'd love your feedback. I'm especially looking for any advice or considerations I might be missing since I'm not super experienced with database modeling. This is an early-stage project, and I want to make sure I don't make structural mistakes that could be difficult to fix later.
Here's the simplified use case:
- Each restaurant can have multiple menus, but I'll focus on one for now.
- A menu is associated with one restaurant.
- Menus consist of groups and items:
- A menu contains groups, which then contain items.
- Items and images have a many-to-many relationship:
- The menu showcases a gallery at the top.
- An image can be linked to multiple items.
- An item can have multiple associated images.
- I'm using sort_order to manage how items are displayed.
- I also plan to incorporate allergen and dietary information in the future (like vegan, gluten-free, nuts, etc.). Any suggestions on how to approach that?
Here's what I have so far:
- Business table: id, name...
- Menu table: id, business_id, name, updated_at, created_at
- Menu group table: id, menu_id, name, sort_order, description, updated_at, created_at
- Menu item table: id, name, description, badges (like vegan), prices (for various sizes/portions), group_id, sort_order, updated_at, created_at
- Menu media table: id, menu_id, path, created_at, updated_at
- Menu item media map: menu_item_id, menu_media_id
I'd like to know:
- Is this structure scalable?
- How should I handle allergens? Would a separate table and join table be the best approach, or perhaps a JSONB column, or just an additional field in the menu_item table? Thanks!
1 Answer
I think your schema looks good overall but seems a bit complex for what’s needed. Most menu displays don't require advanced searching or sorting directly from the database. Consider using JSONB for most of your menu details; it could drastically simplify your data retrieval process while still keeping the necessary structure intact. Just keep the business and menu tables and store the rest as JSONB in the menu table. This might make it easier to work with, especially as things evolve.
I see your point, and I plan to do much of the processing on the client side. However, we might want to implement features in the future that require more complex searches or menu discovery, and I'm worried a JSON structure would complicate maintenance and migrations.