CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Louis B
Louis B

Reputation: 342

How create a favorites table that connects to a customer mysql

I am trying to implement a favorites table in mysql that allows the user to add a restaurant object to the table by id number. Here is the basic outline: There are customers which contain an unique id and other stuff. Then there are restaurants that contain a unique id and other stuff. I want the customers to be able to have favorites. I made a favorites table that has the id of the customer - this is the way I would look up a customers favorites - but then I would have to hard code a set number of columns that I can store the unique restaurant id's in.

Would it make sense to dynamically add columns to the favorites database?

Upvotes: 0

Views: 475

Answers (1)

univerio
univerio

Reputation: 20538

No. This is a many-to-many relationship, and the usual way to implement this is through a join table like this:

users
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jill |
+----+------+

restaurants
+----+------------+
| id |    name    |
+----+------------+
|  1 | McDonald's |
|  2 | Subway     |
|  3 | Chipotle   |
+----+------------+

favorites
+---------+---------------+
| user_id | restaurant_id |
+---------+---------------+
|       1 |             2 |
|       1 |             3 |
|       2 |             3 |
+---------+---------------+

Each row in the favorites table is a "user favorited restaurant" relationship. In this scenario, nobody has favorited McDonald's, John has favorited Subway, and both John and Jill have favorited Chipotle.

SQLAlchemy has excellent support for this pattern:

class Favorite(Base):
    __tablename__ = "favorites"
    user_id = Column(Integer, ForeignKey("users.id"), primary_key=True)
    restaurant_id = Column(Integer, ForeignKey("restaurants.id"), primary_key=True)

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    ...
    favorites = relationship("Restaurant", secondary=Favorite.__table__)

class Restaurant(Base):
    __tablename__ = "restaurants"
    id = Column(Integer, primary_key=True)
    ...

Upvotes: 1

Related Questions