Welcome to WebmasterWorld Guest from

Forum Moderators: phranque

Message Too Old, No Replies

Design thoughts on a "favorites" section

7:32 am on May 2, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120

I would like you guy's / gal's opinions on how to make this more intuitive. Even though it's done in MySQL, I'm putting here because I'm open to ideas either on how to structure the table, OR on how to make the design itself make more sense to the end user.

I have a script set for the user to mark a Classified listing as a Favorite, or they can mark all listings from that seller as a Favorite. In MySQL, the table looks like:

username | category | fave_username | fave_id

Column "username" is the username of the current user, "category" (in this case) will always be "classifieds" (because I plan to use this in other sections of the site, too), "fave_username" will be the user that they've selected to save, and "fave_id" will be the ID of the specific listing they've saved.

There will always be something in EITHER "fave_username" or "fave_id", but NEVER both... if there's something in "fave_username" then "fave_id" will be NULL, and vice versa.

When looking at the list of items, they'll see one button if the item or username is in their Favorites list, and it will show a different button if they have not.

If the user hasn't marked this item yet then when they click the button to save, they'll be presented with a menu:

Favorite this listing
Favorite all from this user

All of that works great, and seems pretty intuitive.

But if they HAVE marked the item or user then, instead of a menu, clicking the button just deletes it from MySQL immediately and then updates the button; eg:

if ($fave_id)
$query = sprintf("DELETE FROM table_A WHERE username='%s' AND fave_id='%s' LIMIT 1",...);

else if ($fave_user)
$query = sprintf("DELETE FROM table_A WHERE username='%s' AND fave_username='%s' LIMIT 1",...);

The coding works fine, but it can get confusing to the end user.

Let's say they marked a seller as a favorite, and that seller has 20 ads listed. The user is looking through their list and wants to remove ONE of those listings from their Favorites. They click the button to remove... but now ALL of the items from that seller are gone, because that seller is now removed from the table.

I need to think of a better way to let them keep the seller in their Favorites, but still remove one item at a time.

I can't just add all 20 IDs to the table instead of the username, because then when they list a new item it won't show up in the Favorites. Or, well, I guess when a seller submits a new ad I could run through the table and add that new ID to each user that has saved him, but that seems complicated and will eventually make for an unnecessarily huge and slow table.

What do you guys think... a programming solution to delete an ID while keeping the seller in the Favorites, or a design solution to make it more intuitive to the end user?
2:40 am on May 4, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120

Sorry guys, I know it's a lot to read, but without posting a link it's hard to explain the problem :-/

The solution I've made so far is twofold:

1. I set up another button and menu for "Hide Listing" and "Hide all from this user", which would overrule the "Favorites".

2. Then, if they have marked a user as a Favorite and try to un-mark a single listing, I give them a pop-up and ask if they really want to do that; if not, then to Cancel and use the "Hide" option instead.

I'm not in love with the solution, but unless you guys can make a better suggestion then I'll probably keep it, at least through the beta testing. Then, maybe a beta tester can suggest something better.
4:13 am on May 4, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member Top Contributors Of The Month

joined:Apr 1, 2016
votes: 837

Here is how I would handle something like this. I use mongoDB so I can't really help you much on the actual queries but conceptually this my approach:

In the user's table (ref user) simply include a reference to the ad ID. Offer the user a button to "fave" another user, but on the click of the button simply get all the id of the "faved" user's ads and add them to the ref user's table. Then when the user returns to the page to view his faves, grab all the id's in ref user's table. If the user want's to delete one, then delete. You can leave a reference to the user that posted the ad. So if the ref user wants to "un-fave" previously "faved" user, you then filter the ref user's table for the user to un-fave and delete all those id's.
4:39 am on May 4, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120

I gotcha... and so, when fave_username posts a new listing with a new ID, it would insert into table_A the new ID for each username that has favored that user. Same with deletes, etc.

I think I would have to make 2 tables; one for favorite_users and another for favorite_id, so when the favored user submits then it would query favorite_users and then insert into favorite_id for each result.

I would also have to create a cron to scan through and delete all of the IDs that have expired; otherwise, the favorite_id table could get huge, FAST. I need to write a cron to delete old photos, anyway, so that's not impractical.

OK... it's tricky and a bit more complicated than I had anticipated, but it can be done.
5:45 pm on May 4, 2018 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
votes: 1124

user table who's doing the favs
faveuser table user needs to like only once (or delete)
favitems table most activity will be here
relate/join as required.
As for management the first two tables are nothing, the third should manage itself (add/delete/expired)

Properly indexed you'd have to hit 1m entries to see any real performance problems.