2

I have a MySQL database that stores user emails and news articles that my service provides. I want users to be able to save/bookmark articles they would like to read later.

My plan for accomplishing this was to have a column, in the table where I store the users' emails, that holds comma-delineated strings of unique IDs, where the unique IDs are values assigned to each article as they are added into the database. These articles are stored in a separate table and I use UUID_SHORT() to generate the unique IDs of type BIGINT.

For example, let's say in the table where I store my articles, I have

ArticleID             OtherColumn
4419350002044764160   other stuff
4419351050184556544   other stuff

In the table where I store user data, I would have

UserEmail             ArticlesSaved                                   OtherColumn
examlple1@email.com   4419350002044764160,4419351050184556544,...     other stuff
examlple2@email.com   4419350002044764160,4419351050184556544,...     other stuff

to indicate the first two users have saved the articles with IDs 4419350002044764160 and 4419351050184556544.

Is this a proper way to store something like this on a database? If there is a better method, could someone explain it please?

One other option I was thinking of was having a separate table for each user where I can store the IDs of the articles they saved into a column, though the answer for this post that this is not very efficient: Database efficiency - table per user vs. table of users

Community
  • 1
  • 1
BrockLee
  • 931
  • 2
  • 9
  • 24
  • Why dont you create separate XREF table for UserArticle, there you can store UserID and corresponding ArticleIDs. – Saba Jan 21 '15 at 15:22
  • Is an XREF table the same as a regular table? I'm still skimming through this website to find out about them: http://www.developerdrive.com/2011/12/the-xref-table-for-mysql/ – BrockLee Jan 21 '15 at 15:30
  • 1
    Yes, just a regular table with cross references. Like this: CREATE TABLE UserArticle(UserID INT, ArticleID BIGINT) – Saba Jan 21 '15 at 15:33

3 Answers3

5

I would suggest one table for the user and one table his/her bookmarked articles.

USERs

id - int autoincrement
user_email - varchar50

PREFERENCES

id int autoincrement
article_index (datatype that you find accurate according to your  structure)
id_user (integer)

This way it will be easy for a user to bookmark and unbookmark an article. Connecting the two tables are done with id in users and id_user in preferences. Make sure that each row in the preferences/bookmarks is one article (don't do anything comma seperated). Doing it this way will save you much time/complications - I promise!

A typical query to fetch a user's bookmarked pages would look something like this.

SELECT u.id,p.article_index,p.id_user FROM users u
LEFT JOIN preferences ON u.id=p.id_user
WHERE u.id='1' //user id goes here, make sure it's an int.. apply appropriate security to your queries.
faerin
  • 1,915
  • 17
  • 31
  • 2
    Perhaps an `unsigned bigint` would be better for an auto_increment idx – Elias Van Ootegem Jan 21 '15 at 15:23
  • Because an id (especially an auto-incremented one) will never be negative (hence `unsigned`) and simply because `BIGINT` allows for _much_ more records to be stored: [cf the signed int max vs unsigned bigint max](http://dev.mysql.com/doc/refman/5.0/en/integer-types.html) => _2,147,483,647_ vs _18,446,744,073,709,551,615_. I know, `signed int` max is enough 99.99% of the time, but failure by success is a dreadful thing, and in this case: relatively easy to avoid (or at least, postpone) – Elias Van Ootegem Jan 21 '15 at 15:32
  • Alright.. I see. I always use autoincrement for my unique ids.. I find it extremely handy and can certainly create quite big numbers anyway. Always filled my needs at least! – faerin Jan 21 '15 at 15:35
  • So would you guys recommend just auto-incrementing an integer that started at zero opposed to using `UUID_SHORT()` for generating a unique ID? – BrockLee Jan 21 '15 at 15:39
  • 1
    @PiJoules: Depends on your DB setup => if you're working on a cluster, you _can_ use auto_increment, but the order of the ids will not always reflect the order in which the records were created. Sometimes you can use a unique index that doesn't contain an AI field, but is a combination of ids that reference related tables. Those tables _can_ use an AI field, though... TL;TR: it's your choice – Elias Van Ootegem Jan 21 '15 at 15:41
  • 1
    My preference is using auto_increment for the ids of each entry, while Elias is suggesting a different approach. Like I said, I find autoincrementing the unique ids (referring to a specifik entry) extremely handy. – faerin Jan 21 '15 at 15:42
  • isnt this a 1-to-1 realtionship? – uberrebu Jul 13 '22 at 10:15
5

"Proper" is a squirrely word, but the approach you suggest is pretty flawed. The resulting database no longer satisfies even first normal form, and that predicts practical problems even if you don't immediately see them. Some of the problems you would be likely to encounter are

  • the number of articles each user can "save" will be limited by the data type of the ArticlesSaved column;
  • you will have issues around duplicate "saved" article IDs; and
  • queries about which articles are saved will be more difficult to formulate and will probably run slower; in part because
  • you cannot meaningfully index the the ArticlesSaved column.

The usual way to model a many-to-many relationship (such as between users and articles) is via a separate table. In this case, such a table would have one row for each (user, saved article) pair.

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
2

Saving data in CSV format in a database field is (almost) never a good idea. You should have 3 tables :

  • 1 table describing users with everything concerning directly the user
  • 1 table describing articles with data about it
  • 1 table with 2 columns "userid" and "articleid" linking both. If a user bookmarks 10 articles, this table will have 10 records with a different aticleid each time.
Laurent S.
  • 6,816
  • 2
  • 28
  • 40