My site has Users. My site has Items. Users can leave a Review of an Item. Users can leave a Rating of an Item. When users rate an Item, they rate it in different categories: a rating for build quality, a rating for usefulness, a rating for aesthetic appeal.
So, in my database, my Reviews table would have one entry and my Ratings table would have three entries per Item per User.
Now on my Item page I want to display the ten most recent Reviews and Ratings.
How can I grab the Reviews and corresponding Ratings and send them all to the view for display?
(I'm using LINQ to SQL).
Thanks.
EDIT: Sorry, thought I had included enough info. Here is some more info about my database. Let's say my Site has items of different sorts. Let's say it contains info on Cars and Books. Obviously when rating a car, you have different criteria than when rating a book.
I am putting all my ratings data into one Ratings table.
I have a RatingsCategories table. It contains two colums:
RatingCategoryID (Identity column)
RatingCategory (name of category)
Category "Cover Art" would apply to books. Category "Performance" would apply to cars.
I have a Ratings table. It contains the following columns:
RatingID (Identity column)
EntityID (Unique identifier among Cars AND Books)
UserID (Identifier of the user who left the rating)
RatingCategoryID (Reference to the category; FK from the RatingsCategories table)
Rating (int; the rating given)
DateModified (DateTime)
So, this table can contain all the ratings of the books and the cars. One book might have three different ratings per user (Cover Art, Content, Binding Quality, etc) and one car might have three different ratings per user (Appearance, Performance, Ease of Maintenance, etc)
Likewise my Review table can contain reviews for Books and Cars. It looks like this:
ReviewID (Identity column)
EntityID (Unique identifier among Cars AND Books)
UserID (Identifier of the user who left the rating)
Body (Text of review)
DateModified (DateTime)
User's can Rate and Review each item once. So, if when a site visitor navigates to a Book's page, I want to display the last 10 Reviews and corresponding Ratings. That means I need to grab the 10 most recent Review records for that Book. I also need to grab the ten most recent full Ratings for that book (same EntityID and same UserID as the Reviews) which could be three records for each Rating.
Does that make sense?
What's the best way to do this?
Am I crazy in this approach and database schema?
Thanks for your patience.