CODEWITHSUNDEEP
mysqldatabaseschemarelational-databasedatabase-schema
Kevin Cooper
Kevin Cooper

Reputation: 5395

How to implement a 'Favorite' relation in MySQL database?

I have an app which displays lists of Performers and Venues. A User of the app can choose to 'favorite' as many of each type as they want. I am wondering if there is a standard way to implement this in a MySQL database. Here are my options:

  1. Have a single table Favorite, storing UserID, Type, TypeID
    • Type would be either 'Performer' or 'Venue'
  2. Have a single table Favorite, storing UserID, PerformerID, VenueID
    • Only one of PerformerID or VenueID would be allowed to have a value for each entry
  3. Have two tables Favorite_Performer and Favorite_Venue, each storing UserID, PerformerID or UserID, VenueID
    • I think this would be the best way to do it 'relationally.'

What are the advantages and disadvantages of each method, and which would you recommend in terms of future maintainability? Thanks.

Upvotes: 2

Views: 1868

Answers (1)

spencer7593
spencer7593

Reputation: 108450

Option 3 is the cleanest. It makes adding and removing a favorite the easiest. And this is the schema that an ORM framework would implement.

Option 1 is workable, but you couldn't define a foreign key constraint to enforce integrity. Adding a predicates WHERE type='Venue' to your queries, makes working with that table almost like working with a separate 'Favorite_Venue` table.

Option 2 complicates adding a favorite, if one already exists. (This would require an update to existing row, rather than insert. If someone had two favorite venues, and five favorite performers, how many rows would that be? But at least foreign keys could be defined.

Say we had two favorite venues and two favorite performers:

userID  venueID  performerID
------  ------- -----------
     1       11          177
     1     NULL          654
     1       12         NULL

When favorite venueID 11 is removed, you would update the row to set venueID to NULL. But if venueID 12 is removed as a favorite, would you set the column to NULL, or delete the row. When you went to add a favorite performer, would you update an existing row that had performerID NULL, or would you insert a row. Not unworkable, but it's more complicated.

If we had a rule that said only one of the columns venueID or performerID could be populated, then using predicates like WHERE venueID IS NOT NULL would make working with this table almost like working with a separate Favorite_Venue table.

Bottom line, I'd go with option 3, unless there was some compelling reason not to.

It's not "clutter". Each of those tables Favorite_Venue and Favorite_Performer has a purpose, a "raison d'etre", a reason that it's there. Each table resolves a many-to-many relationship.

Combining these two separate "relationship" tables into a single table actually creates clutter. If we add a column to distinguish between the rows (to effectively answering the question, which table does this row really belong in), that column is "clutter". If we use two separate columns on the same row for the two relationships, that creates clutter in the code that deals with adding or removing favorites.

Upvotes: 3

Related Questions