DB Schema

[rsoldner]

Feedback for your schema: I'm not quite sure what you were planning with the search table. From what I can interpret, it's more of a history table.

The thing I think the goal of your software is to look at a users history and make a recommendation for where to go. It could be a new restaurant or one they love.

user table (imported)

restaurant

• name
• other meta about the restauarant.. like food type (mexican, italian) or sit down vs fast food, etc

history

• user_id
• restaurant_id
• when visited
• rating enum( hate, like, love )

times_gone can be calculated based on your history. not sure what 'trying' is..

[Beahmer89]

I guess what I was planning on doing with the search table is basically the equivalent of a history table except I am keeping more information about the actual searches. Yet your suggestion seems cleaner than mine.

My only thinking with the search table is a way to group particular restaurants together. It would be up to the user to decide search terms and locations. So example:

User ->Creates Search 'Pittsburgh Restaurants' Restaurants include: Mad Mex, Uncle Maddios, Spak Brothers, Eden, B52

Suggestion algorithm of places you have been, would pick from groups and suggest a particular place based on rating, etc. Idea would be to have user categorize places they go to in groups and it would be up to them what items and locations they allow in the groups. So if I put a restaurant in a Pittsburgh group that is really in St. Louis, its a possibility the St. Louis restaurant could come up in a suggestion of places to go back to that I have been.

For your schema, as I write this out, I realize if you add a zip/location to the restaurant table. Then you could just add restaurants to your history and it is search agnostic. Then if you want to make suggestions based on where you have been, they can enter criteria based on whats in the database.

Yet adding zip/location adds some complexity that may cause the restaurants table to be normalized more. If you have been to a Mad Mex in Cranberry and one in Pittsburgh, they have different zips but are duplicated in restaurants table.

This is a dump of my thoughts on your schema suggestion and mine. Let me know your thoughts on where I was going and if it made sense. I may just go with your suggestion, rather than what I originally planned, but it is late and things are starting to not make sense. Will revisit tomorrow.

[rsoldner]

So you could also have a search table to keep track of what searches they did.

I think it would be beneficial to have them separate, like search_history and visit_history. That allows you to keep track of what they're searching for (you'll be a mini google) and what they actually picked.

To determine the rating, you'd be sending some kind of post-visit notification.. Starbucks does something like that, the app creates a notification and asks me if I want to leave a tip.

If you're using the yelp API, you may not even need to store the restaurant information (depending on what kinds of information you can get from their API). You just want to keep track of where they've been and what they've liked. If you wanted to suggest new places based on what they like, that is still probably doable with just the visit_history, but would take some query work.

[Beahmer89]

Hey @rsoldner, I pushed up come changes to the schema. Let me know what you think :) check it

[Beahmer89]

Looking to move forward with the db schema that I have updated now. @rsoldner if you have any comments please let me know, I would love to address them, but if I dont hear from you I will be closing this issue.