What is the best way to hold a user’s “favorites” in the database?
Since the user can bookmark more than one item (or company, in my case), there are a few options I have thought about:
1. A “Favorites” field in the user settings
In the same table as the user is stored in, an additional varchar field is placed containing the subscribed notifications stored either as XML or comma separated fields. This might be inefficient since the company IDs are stored as strings instead of integers.
2. An entirely new “Favorites” table
This could get messy. Something along these lines:
--------------------------------------------------
| userID | bookmarkedCompanyID | notifications |
--------------------------------------------------
| 125 | 58347 | email |
| 125 | 34757 | email |
| 125 | 58347 | none |
| 125 | 47222 | none |
| 125 | 44444 | email |
| 57 | 22436 | daily |
| 57 | 56782 | daily |
| 782 | 58347 | email |
| 57 | 34778 | weekly |
| 125 | 32111 | email |
--------------------------------------------------
This would allow additional properties to be added, however, it seems a bit cluttered, and if each user has 30 bookmarks, it could start gobbling up space.
Are there any other options? What is common?
It’s a bit of a time rush, so **any **help is appreciated…
