Efficient way in structuring database for restaurant type app

Hi

I have a problem in deciding how to structure the database to hold all the information in the app I’m making so the information can easily be captured and retrieved.

These are the data inputs I need to store and retrieve:

  • Restaurant name, phone number, address, website

  • Restaurant’s menu categories (like starters, mains, desserts etc.), along with the dishes related to those categories, and the dish descriptions that are related to those individual dishes.

The way I currently thinking of structuring it, is like this:

  • Database Table 1 storing: Restaurant name, phone number, address, website

{With a linking value between the tables of course}

  • Database Table 2 storing: Restaurant menu categories, dishes (related to those categories), dish descriptions (related to those individual dishes).

But I feel like scaling the storage structure in this way would not be optimal, as database Table 2 would have to store all dishes each with single row (with columns to store the categories and dish descriptions) and if, for example there are a 1000 restaurants each with an average of 25 dishes, there will end up deing 25000 rows. Is this how it’s just going to have to work? or can it somehow be structured differently?

I would have;

Restaurant
List of Menu Sections

Menu Sections
Menu Section Name, List of Sub Sections, Link to Restaurant

Menu SubSection
Menu Subsection Name, Link to Menu Section

Dishes
Dish details, Link to menu section.

The reason I would split it further because you might have Main Courses,->Beef Dishes, Fish Dishes etc. Also you might want to subset Vegetarian options etc.

1 Like

I see, nice. :+1:

But now say I want to have another table that records the ratings of each dish by users, how would I ensure that specific ratings stay with each dish, such as in the case where there might be two different restaurants that happen to have the same dish linked to the same category.

In your dishes table each dish will have a uniqueid generated by bubble. You can use that ID to refer back on your ratings table so that each dish is tracked independently of the restaurant.

1 Like

If you are talking about a restaurant chain having the same menu across their chain, the I would setup a table ratings with the columns

Restaurant Dish Rating Comments RestaurantFeedback

Or something like that.

Here are some considerations.

A single table with a large number of items can be a problem, but I’m not aware of Bubble publishing benchmarks. I believe that a while ago they extended the number of things a single table can hold. From 25K to 50K?

The biggest issue is search and sort time. In most databases, you can specify fields to index, which speeds up search and sort on those fields. I don’t believe Bubble gives us that option (it should), and I don’t know if Bubble does its own indexing. (It probably does index linked fields at least. Just guessing.)

Ultimately, you have two choices. You can have a database record for every dish of every restaurant. Then you can’t really restrict how much it grows, unless you decide to store only the top five or ten dishes from each restaurant. The other option is to create categories of dishes, like Salmon main, Steak main, salad, etc. You’ll still need a record for every restaurant’s salmon, but that record will now only need a link to the category, plus a rating.

DaveA is giving a good theoretical model, but it has some problems. Some restaurants don’t have subsections, and that will confuse things a lot. I would rather make those fields in the dish table. You could still search and sort on them. Join tables work, but they’re a pain. If they don’t give you specific functionality, avoid them.

Now that I look at your question again, I realise that was your approach. I think it’s best.

1 Like

I was referring to the possibility(by chance) that multiple restaurants (totally unrelated to each other “not part of the same franchise”) having dishes with the same name within categories of the same name.

But yes franchises with similiar or same menus is also another hurdle regarding storing ratings for the same dish with ratings unique to a specific restaurant within a franchise…

I see. Hmmm

Great thing about Bubble is no need to know coding, (still new to bubble).

Building the website/webapp is the easy part, but as I’m using bubble more and more to realise my ideas, its becoming apparent that designing the database is becoming a nightmare, especially when I’ve never designed one before.

And the way I want my web app to work seems like making a very complicated database to handle all that data.

It bothers me a lot that Bubble has not, to my knowledge, published a document with guidelines on database design for relational databases. Also, none of the independent courses that I’ve seen treats this subject either.

I would just take it slowly…
Just build one part of your app at a time and make the database work for that part.
This way you can learn as you go and keep it tightly focused.

Problem is that this initial data that I need stored is the core MVP functioning of the app. There’s definitely more I want to add in the future that would require more tables and links. But without this initial data the app is useless, and this in of itself is already proving extremely complicated.

So you might want to consider 2 paths then:

  1. You continue to do it but expect lots of work to get it done - I’ve been making my app which is like Tripadvisor for Startups - estimate it took 40 hours to ramp up and 4 months of work to get to beta MVP deployment stage - where I’m sitting now. I prefer this approach because it saves money and you learn so you can make anything you want in the future with bubble.

  2. Hire some of the bubble freelancers to help you jumpstart your project. This could work out well if you need the initial complexity distilled down and then you can go back and understand how it works as they are building it for you. Of course it does require money and you have to go back and figure out how it worked if you want to make future changes.

Also to @matchen point - I agree bubble makes it look easy and it is - but on the backend things can get complex very quickly if you haven’t thought about normalizing the data and the relationships. If you’re not familiar with those concepts it’s an extra hurdle that’s not entirely clear from the start. But like everything about bubble I’m 100% certain it will be improved upon and made easier.

I opted for a simple database structure for my restaurant app. This is how I structured it.


This does not work with restaurant-specific dishes yet. To add this to the database, I would add a field (a list of “store_address”) under “item”. After a user has selected a restaurant, all the items would have an additional “store_address” filter.

Link to live demo and Bubble editor