I currently have (tables changed):
UserID uniqueidentifier, CompanyID uniqueidentifier, (for future reference) .. more unrelated fields (PK: UserID)
UserID uniqueidentifier, MovieID uniqueidentifier, RatingTypeID uniqueidentifier, Rating tinyint (PK: UserID, MovieID, RatingTypeID)
RatingTypeID uniqueIdentifier, Title varchar(25) (PK: RatingTypeID)
The RatingType table contains "Favorite", "Need to Watch", etc. So actual Rating value is how much they liked a Favorite, or how badly the want to watch a movie etc.
Now I introduce a new table:
CompanyID uniqueidentifier, .. more unrelated fields
The idea is that if a user becomes part of a company, due to requirements that cannot change, all the Ratings need to be Company ratings and not User Ratings. I'm not a normalization guru, but for some reason creating another table (below) seems redundant, but maybe I'm thinking to much into it.
CompanyID uniqueidentifier, MovieID uniqueidentifier, RatingTypeID uniqueidentifier, Rating tinyint (PK: CompanyID, MovieID, RatingTypeID)
This feels incorrect because it almost matches the rating table and if I need to do any averages it becomes more difficult to average across two tables. Is there a better design for my needs?