3

Possible Duplicate:
Is storing a comma separated list in a database column really that bad?

This is a problem I often encounter when trying to expand the database.

For example:

I want to keep track of how many users saw a particular article in my website, so in the database I added a views field to the article table. Now, if I wanted to make sure that these are unique views then this is clearly not enough.

So let's say that in my design I can identify a user (or at least a computer) with a single number that's stored along with an IP or something.

Then, if I wanted to keep track of how many unique users saw a particular article which is the best way to go?

  • To create another table article_views with the fields article_id and user_id.
  • To save the user_ids separated by commas inside the views field of the article table.
Community
  • 1
  • 1
federico-t
  • 12,014
  • 19
  • 67
  • 111

3 Answers3

7

Never, ever, ever choose the separate-by-commas solution. It is a violation of every principle of database design. Create a separate table instead.

In your particular case, create the table with the PRIMARY KEY on (article_id, user_id). The database will then prohibit the entry of duplicate records. Depending on your SQL engine, you can additionally use INSERT OR IGNORE (or equivalent) to avoid throwing exceptions.

The other solution requires you to enforce the uniqueness in the all applications that touch the data.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • 1
    @john,Larry- NOOBs need to learn 1NF and why it's good for you. In this case, one concept is worth a thousand tips and tricks. As Date says, "theory IS practical". – Walter Mitty Nov 07 '12 at 11:45
2

Don't use comma separated values. Ever. Create a separate table that links all of those Ids to the article viewed.

Pretty simple design. It will be a table with two columns, both foreign keys. One to the article table and the other to the user table.

Yatrix
  • 13,361
  • 16
  • 48
  • 78
0

Have you considered using 

SELECT COUNT(DISTINCT User + IP) As UniqueViews FROM Views GROUP BY ArticleID

If your views table contains duplicates like this records against User and IP (i.e. 10 per cilck per day or something), then COUTN(DISTINCT) will count the distinct occurences of them, not the number of records.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91