MySQL: More Columns vs. More Queries

So I'm trying to design a rather large database to handle videos and their tags. Because only a finite number of tags can be selected for a video (27) I am storing them in a table 28 columns wide (one column for each tag + the video_id).

My problem arises when I want to get the number of times a user has liked/disliked a video with a certain tag (lets say #24). Each Like/Dislike gets a row in another table.

Here are the two options I've considered.

1) Add 27 more tinyint columns to the User Table, each one representing the number of times he's voted on a video with tagX.

PRO: Easily Selected

CON: Doubles the amount of data needed for each user

2) Use Inner Joins

PRO: Keeps size small

CON: Harder on the system

Both because I'm optomistic and because I'm trying to use goo开发者_如何学运维d practice, I'm trying to optimize my databases for 300,000+ users, and these numbers will be needed a lot. I Did the math and even with 300,000 users it would only add about 27 megabytes to my database.

What to do!?

(this is just my opinion)

I think you're optimizing too early (and probably incorrectly). Joins are not that expensive. You should have a users table, a tags table and a like table. Depending on your exact queries, you will probably find this marginally less expensive.

You are better off spending time ensuring you have the right indexes than creating a layout designed for performance over maintainence.

Your denormazling rationality is flawed. Today you might think that 27 videos is the maximum but there will always be another one or two or more in the future.

27 columns to user table only work if you have 27 different tags. So, adding another tag requries schema change (which is a bad thing).

I vote for something like this:

table users(
   user_id
  ,primary key(user_id)
);

table videos(
   video_id
  ,primary key(video_id)
);

table tags(
   tag_id
  ,tag_name
  ,primary key(tag_id)
);

table video_tags(
   video_id
  ,tag_id
  ,primary key(video_id, tag_id)
);

table user_likes(
   user_id
  ,video_id
  ,primary key(user_id, video_id)
);

To show the number of votes per tag, you would do something like this:

select c.tag_name
      ,count(*)
  from user_likes a
  join video_tags b uing(video_id)
  join tags       c using(tag_id)
 where a.user_id = ?
 group 
    by c.tag_name;