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:
$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?