0

I'm making funny app.

User can mark movie as seen.
User can rate movie (1-10).
When user rate movie it is automatically marks as seen.
User can't remove movie from seenlist if movie is rated by him.

User have 2 tabs: seen movies which have movies in seenlist and rated movies. And tab with rated movies for movies that has rating.

I was going to make 2 tables:
ratings (user_id, movie_id, rating, created_at)
seen(user_id, movie_id, created_at).

But I don't like to duplicate data, so I kind of think maybe single ratings table will be fine, if user wants only mark as seen, rating value will be 0 or -1.
One think will be is to make where clause when calculating movie avg rating (where ratiny >0). But everything else is done dryer.

What do you think is better single table or separate tables?

1
  • I would caution against using unobvious values to indicate states that are in conflict with the column name, i.e. using a rating column to actually indicate if a movie was seen or not. Each distinct data point should be a separate column. You can probably be fine using one or two tables, you might want to think about your queries you'll be running and if you would see improvements from querying the data separately or not. Dec 15, 2018 at 22:27

1 Answer 1

0

What does created mean? When the record was created? If so, of what use is that? Perhaps it means when_seen (or rated)?

I see no necessity to separate the two tables.

Since seen is not "optional", and rating is, consider

rating TINYINT UNSIGNED NULL  COMMENT '1..10'

Where the column is NULL until the user specifies a rating.

NULL has the advantage that

SELECT movie_id, AVG(rating)
    ...

will ignore the NULL values and get the 'correct' average.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.