Hello!
Like youtube, i have a usertable, a table with objects (videos) and a table with categories.
Now, I want a user to be able to subscribe to a category, but how do I do this effectivly? I would need a table for keeping track of which categories a user subscibes to (many-to-many relationship), but I also need to keep track of which objects (videos) that a user hasn't watched yet, so how do I do that?
Why not use RSS? That way, a user can manage their own subscriptions and you don't have to store all the subscription info and then run a process based on these subscriptions. Their RSS reader will take care of that for you.
This CodeProject article has some details about how to serialize your classes into RSS.
Just have another table that tracks which videos a user HAS watched. Then when selecting which videos to show to the user check them against the watched video table to make sure they haven't seen it yet.
You can then also use this table to show the user a list of all videos they have watched, or display a complete list of which videos are currently being watched on the site etc.