CODEWITHSUNDEEP
mysqldatabase-design
OllyBarca
OllyBarca

Reputation: 1531

MySQL - Database structure, not sure how to do it

I am trying to make a database of Users and Movies. Then I want to allow users to click on a movie and mark it as "watched".

What would be the best way to go about structuring this. I mean, could I simply have a table called "Watched" and simply link the UserId to the MovieId in that table to signify that the user has watched that particular movie. This would mean that every instance of a movie being watched will need a record in the table, right? It seems like it could end up taking up ALOT of space in the database.

Is there not an easier way to do this? Sorry if it sounds abit vague. I'm thinking its kind of similar to "friends" on facebook. Would there be a table somewhere where each instance of a friendship occuring is recorded as a record, or is there a way of simply having a list of friends lookup, or something, i don't know... any help much appreciated, thanks!

Upvotes: 0

Views: 41

Answers (2)

Kuzgun
Kuzgun

Reputation: 4737

You need a user table, movies table, friendship_match table and if you want to record which movie watched at which time, a watch table (with this one you can also display watch history for users)

You have to have a table for friendship because it will contain requests also. If a person adds someone, it doesnt mean the other person will accept it. Other than this, it will take really take a long time if you query the whole users table.

Upvotes: 0

Nadeem_MK
Nadeem_MK

Reputation: 7689

Your suggestion is correct, using table called "Watched" and simply link the UserId to the MovieId in that table.

In this way, you will have a normalised database, with one-to-many relationship on both sides, toward the "Movies" and "Users" tables.

It will also save you performance cost also when retrieving data later, in case your database becomes a really large one.

Upvotes: 2

Related Questions