How to change type from one owner to two different types of owners

Question

I currently have (tables changed):

User

UserID uniqueidentifier,
CompanyID uniqueidentifier, (for future reference)
.. more unrelated fields

(PK: UserID)

UserRating

UserID uniqueidentifier,
MovieID uniqueidentifier,
RatingTypeID uniqueidentifier,
Rating tinyint

(PK: UserID, MovieID, RatingTypeID)

RatingType

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:

Company

 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.

CompanyRating

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?

Answer 1

I have a feeling you will benefit if you borrow party concept from Party-Role-Relationship, one of universal data models, which is capable of keeping all information you needed.

So you will have party_type table that will currently have 2 records, person and organization, one common party table that holds all people and companies (you may want to add 2 more tables , person and company that hold attributes specific to people and organizations respectively), and your rating will have a reference to party.

Answer 2

I decided to use an inheritance model.

User

UserID uniqueidentifier,
CompanyID uniqueidentifier, (for future reference)

(PK: UserID)

Company

CompanyID uniqueidentifier,

Rating

RatingID uniqueidentifier,
MovieID uniqueidentifier,
RatingTypeID uniqueidentifier,
Rating tinyint

(PK: RatingID)

UserRating

RatingID uniqueidentifier,
UserID uniqueidentifier,

(PK: RatingID, FK: UserRating_Rating)

CompanyRating

RatingID uniqueidentifier,
CompanyID uniqueidentifier,

(PK: RatingID, FK: CompanyRating_Rating)

RatingType

RatingTypeID uniqueIdentifier,
Title varchar(25)

(PK: RatingTypeID)

The Data model itself allows users to have individual rating while also being in a company with it's own rating, and that is ok as it might be needed in the future. For now this allows me to aggregate values by MovieID and RatingTypeID, and with little work both UserRatingID and PartyRatingID.