-2

I have a web application in which each user is allowed to add its own items. These items are not public or shared/visible accross the users. In other words, if user 1 creates an item, that item is only belongs to user 1.

[table: item]
id     title     content
1      ...       ...
2      ...       ...
3      ...       ...

[table: bookmark]
item_id   user_id
1         1
2         1
3         2

I need a "bookmarks" page which shows those items that the user has bookmarked. Does it make sense to use a separate table bookmark like I did above?

Zoltan King
  • 1,964
  • 4
  • 18
  • 38
  • You're going to have 1 item to 1 bookmark so you could achieve the same exact thing by adding your `user_id` field to your `item` table. This would be different if your bookmarks were public and there was a 1 item to Many bookmarks. – dvo Jan 17 '20 at 17:05
  • You're right. I'm not sure what name should I give to the column, though. If I use `user_id` in the `items` table I think that would be too generic and `user_id` might appear used for other purposes in that table in the future. – Zoltan King Jan 17 '20 at 17:11
  • Personally, I would name the table `Bookmarks` using the `Item` table's structure + `user_id`. But, the structure you asked about with two tables is fine too. It's really up to you and your design preferences. – dvo Jan 17 '20 at 17:13

1 Answers1

2

If you use create a new column named bookmark and bookmark_reference of the type uniqueidentifier for both tables, and your code simply puts a GUID (or the SQL function NEWID()) when a user does a bookmark.

Your bookmark table replace item_id with bookmark_reference and another varchar(100) column table_reference.

Thus making your bookmarking functionality generic for the whole system, any table, can be used for bookmarking or any other text the user would want to record against this particular record of this particular table. Like custom 'notes' against a customer for just this user. To make a "public" bookmark/note, just use either a zero or a null in user_id.

Just giving you basic ideas. The ERP Syteline from INFOR uses this type of functionality, but they use a guid (uniqueidentifer) for the user_id equivalent field, making the table unreadable without a INNER JOIN to the users table. So using user_id will make everyone's life easier down the road.

So the structure would be like this:

[table: item]
id     title     content     bookmark
1      ...       ...         {guid abc}
2      ...       ...
3      ...       ...

[table: bookmark]
bookmark_reference     table_reference   user_id
{guid abc}             item              1
{guid def}             item              1
{guid xyz}             customer          2
{guid 123}             user              1

Fun fact with this structure, a user could have a bookmark on a user!

Edit: Link to a StackOverflow article on using uniqueidentifier

Mark Derail
  • 59
  • 4
  • Interesting. I've never heard of this method. I'll try to implement it in PostgreSQL, although it doesn't seem to have any built-in function to generate UUIDs https://www.postgresql.org/docs/9.1/datatype-uuid.html – Zoltan King Jan 17 '20 at 18:17