You could always do something like this:
Create a users table to store all of your user info:
create table users
(
id int,
user_name varchar(50)
);
insert into users values
(1, 'John'),
(2, 'Jill'),
(3, 'Bob'),
(4, 'Sam');
You state that you might need to apply this type of tracking for multiple applications. So you can create an application table to store the app name and any details.
create table applications
(
id int,
app_name varchar(50)
);
insert into applications values
(1, 'App 1'),
(2, 'App 2'),
(3, 'App 3');
Next create a categories table to store the info on each of the categories that you want to track by the users. This table will include a column that will contain the application id. This table will contain a foreign key relationship to the applications table. This allows you to have some flexibility in having the same category in multiple apps.
create table categories
(
id int,
cat_name varchar(100),
a_id int
);
insert into categories values
(1, 'Cat 1', 1),
(2, 'Cat 2', 1),
(3, 'Cat 3', 2),
(4, 'Cat 4', 3);
Finally, create the users_categories table which will contain the stats that you want to track, including user, category and then the action_date (the date the event took place).
create table users_categories
(
u_id int,
c_id int,
action_date datetime
);
insert into users_categories values
(1, 1, current_timestamp()),
(2, 3, '2012-01-15 10:15'),
(3, 4, '2012-08-24 12:48'),
(1, 2, '2012-06-08 08:50');
Then to query the data, you would use something similar to this:
select *
from users u
left join users_categories uc
on u.id = uc.u_id
left join categories c
on uc.c_id = c.id
left join applications a
on c.a_id = a.id
And the results would contain (See SQL Fiddle with Demo):
ID | USER_NAME | U_ID | C_ID | ACTION_DATE | CAT_NAME | A_ID | APP_NAME
------------------------------------------------------------------------------------------
1 | John | 1 | 1 | '2012-09-28 06:47:53' | Cat 1 | 1 | App 1
1 | John | 1 | 2 | '2012-06-08 08:50:00' | Cat 2 | 1 | App 1
2 | Jill | 2 | 3 | '2012-01-15 10:15:00' | Cat 3 | 2 | App 2
3 | Bob | 3 | 4 | '2012-08-24 12:48:00' | Cat 4 | 3 | App 3
4 | Sam | (null) | (null) | (null) | (null) | (null) | (null)
A few other suggestions, you could technically remove the a_id from the categories table and create a join table between categories and applications, similar to this:
create table categories_applications
(
c_id int,
a_id int
);
insert into categories_applications values
(1, 1),
(2, 3),
(3, 1),
(1, 2);
This would lead to one additional join in your queries, but the results would be the same:
select *
from users u
left join users_categories uc
on u.id = uc.u_id
left join categories c
on uc.c_id = c.id
left join categories_applications ca
on c.id = ca.c_id
left join applications a
on ca.a_id = a.id
Best Answer
The question to ask yourself is simple: Can a single Transcode record be associated with more than one Movie or Episode?
If Yes, you need a linking table that links Movies and Transcodes and one that links Episodes and Transcodes to create a Many-To-Many relationship.
If No, you simply add transcode_id and a foreign key to the Movies and Episodes tables.
You need to apply that same logic to some of your other tables/relationships. For example, can a Movie really only be associated with a single user as per the current design? What is that relationship - the user who uploaded it, a user who watched it, a user who recommended it?
A few others:
Check out this Wikipedia page which covers off some basics on database/ERD design. You need to identify the various entities within your data model (a Movie, for example, is an entity, as is a user) then define the relationship between each entity (Entity A can have only one Entity B associated, and Entity C can have many associated Entity D records etc) and that will help you determine and design your ERD.