January 16, 2008 at 8:47 pm
What approach do people take with this, I imagine, common problem?
A simple table setup
--------------------
Show (theatrical kind) - has many productions
Production - many actors
Actor - many productions (through pivot table)
User, not related (yet)
'Aspect' to implement in the db
------------------------------
Users can write reviews on Shows, Productions or Actors (plus many other entities excluded for simplicity).
Approaches:
------------
1. Have a generic table, i.e. 'review'. Then, for each entity, have a table that 'derives' from the 'base' table, using the foreign key as it's primary key, and having a foreign key to the entity it is reviewing. So:
Review: ReviewID (PK), UserID (FK), Title, Body, Rating....
ShowReview: ReviewID (FK/PK), ShowID(FK)
ActorReview: ReviewID (FK/PK), ActorID(FK)
ProductionReview: ReviewID (FK/PK), ProductionID(FK)
2. Have a single table for the review that contains a nullable FK for each entity that the review could relate to:
Review: ReviewID (PK), UserID(FK) NOT NULL, ActorID(FK) NULLABLE, ShowID(FK) NULLABLE... etc
3. Have a single table with a single foreign key to represent any of the entities that the review is about. A third foreign key references a 'reviewType' lookup table. Eugh I know, but it is possible.
4. A better solution / variations & corrections to the above (?)!
I realise this is probably a common problem but I have been searching for hours on it (my googling sucks) 😮
Thanks for any advice that can be given (articles & links to good google search terms welcome!),
Dominic
January 21, 2008 at 4:03 am
I think every table should contain facts from the reality that we keep data abot.
Review is not exact since it can be a Review of anything, a play, a car, abook etc.
Thus I would do tables like ActorReviews, PlayReviews etc.
And that makes it possible to have clear and nonnullable relationships.
/m
January 21, 2008 at 7:30 am
Agreed. So would you go with the first option or are you saying you would scrap the 'generic' review table altogether?
I like the first option as it enables you easily to get an index of all a user's reviews and also to easily get all the reviews for a given item.
Thanks for the response,
Dominic
January 21, 2008 at 7:45 am
I would scrap the generic table altogether.
Given a table called ShowReviews there would be a table called Shows.
In the table Shows I would have a column like ShowReviewId.
Which would be zero/NULL if no Review had been done.
If there could be multiple Reviews for a Show I would have a relation table.
I would do a union/join if I wanted to know number of reviews for Actors and Shows.
My experience is that it can be difficult with proper DRI in a generic table solution.
/m
January 21, 2008 at 8:08 am
Ok thanks. You would not need an extra relation table for a many reviews to one show relationship though, it is not a many to many. With this approach you would just have tables like:
ShowReviews
-------------
ShowReviewId (PK)
ShowId (FK)
UserId (FK)
Title, Body, Rating, etc.
I think I prefer the first generic approach as you still have the usefulness of the 'descriptive' tables (ShowReview, ActorReview, etc) but you group all the common fields together in a single table which has advantages for easy updating, etc. Queries for both angles that you may want to get the data from are both straightforward too, i.e.
Query to select all reviews for a show:
-------------------------------------
SELECT r.ReviewId, r.UserId, r.Title, r.Body, r.Rating
FROM ShowReview sr
INNER JOIN Review r ON r.ReviewId = sr.ReviewId
WHERE sr.ShowId = @showId
Query to select all reviews a user has written:
--------------------------------------------
SELECT r.ReviewID, r.Title, r.Rating
FROM Review r
WHERE r.UserId = @userid
Dominic
January 21, 2008 at 12:23 pm
I would want to have a little more information about how the data will be used in order to make an absolute commitment; however, I would have to wonder if a review of a given Actor should not actually be a review of a given Actor in a specific Show. After all, a review of John Wayne based upon his earliest work would not be the same as one based upon his later work. Thus, I would tend to think that you would want something more like:
tblShows with data like ShowID, ShowName, Synopsis, etc.;
tblProductions with data like ProductionID, ShowID, ProductionStartDate, ProductionEndDate, Location, etc.;
tblActors with data like ActorID,ActorName, BirthName, BirthDate, RecentCredits, etc.;
tblProductionActors with data like ProductionID and ActorID;
tblReviewers with data like ReviewerID, ReviewerName, etc.;
tblShowReviews with data like ReviewID, ShowID, BriefReview, Reveiw, Rating, DateOfReview;
tblProductionReviews with data like ReviewID, ProductionID, BriefReview, Reveiw, Rating, DateOfReview;
tblActorReviews with data like ReviewID, ActorID, ProductionID{?}, BriefReview, Reveiw, Rating, DateOfReview;
You will notice that I have separated the Show, Production and Actor Reviews (although the ActorReivews might have a FK link to the Production as well). This is because I would expect each of them to be logically separate. In other words, if someone is reviewing a Show, I would expect this to not be a Production specific review and, especially, not an Actor specific review. Similarly, a review of a Production, while it might contain some general comments about the Show or the group of Actors involved in the Production, should probably actually be about the . Finally, my iniial take on the review of an actor would be that it should be related to the Actor's performance in a particular Production but should be specific to that Actor's performance. (I would think that the general blur that one finds in, for instance, a program for a play should be taken from the Actor's RecentCredits entry.)
Of course, this sturcture would mean that one would need to UNION the data from the various revies tables in order to provide the totality of the Reviewers comments. However, it would make more sense (to me, at least) to group the Reviewer's entries by Actor within Production within Show or by something similar (possibly a chornological or reverse-chronological order?) in a report.
January 21, 2008 at 1:18 pm
Yeh, this is almost exactly as I have it (with the exception of the review tables).
I don't see the advantage of having all those review type tables with exactly the same fields being spread out like that though. What is wrong with having a generic Review table and then having all the specific review tables 'inherit' from it (for want of a more relational word)? You still have a table for each review type; indeed, if you were to follow the logic of having reviews of actors in a given show - the ActorReview table would appear like this:
ActorReview
------------
ReviewId (FK / PK)
ActorId (FK)
ProductionId (FK)
It certainly makes sense from an OO standpoint but I realise that an RDBMS is not OO. However, relationships are fully defined here and this way of working does not break the relational model.
Can anyone argue specifically against this approach?
One thing I see as a potential problem is that logically you could have an 'ActorReview' and a 'ProductionReview' that share the same ReviewId which is not desired. In fact (thinking aloud here), that might just sway me to having all those seperate tables. Anything else wrong with it?
January 21, 2008 at 7:29 pm
One thing I see as a potential problem is that logically you could have an 'ActorReview' and a 'ProductionReview' that share the same ReviewId which is not desired. In fact (thinking aloud here), that might just sway me to having all those seperate tables.
The point of the approach that I took was that I considered the various reviews to be separate categories of the same basic oncept (Reviews are Generic but ActorReviews are specific) and the usage I pictured was having a Reviewer log into an application that would allow the highest level review (that of Show), followed by the next highest (Production), and, then, a review for as many of the Actors in the Production as the Review chose to review. Similarly, in looking at the drill down aspects, I thought of the ShowReviews as being the 50,000 ft view, the ProductionReviews as the 5000 ft view, and finally the ActorReviews as the ultimate detail reviews.
However, by reversing the sequence, an ActorReviews entry could be traced back to the ProductionReviews, and, then, on to the ShowReviews entries. This would, IMHO, allow the most flexibility without engaging in self-joins of tables (something I try to avoid ;-).
Anything else wrong with it?[/
]
If you are talking about the "Generic Review Table" approach with various FK's that link the entries back to actors, Productions, or Shows. What happens when you realize that there is a need for a Set Design Review, a Reviewer Review (how I would love to do a couple of those ;-), a Theater/Theatre Review, a Choreography Review, and a Lyrics Review? Do you continue to add FK's ad nauseum? Or do you handle those in their own tables (because of the need for additional, special columns)? Also, there is no need to force the various Reviews I mentioned to have exactly the same columns. For instance, a ShowReview would not necessarily have a TheaterID but a ProductionReview probably would and an ActorReview probably wouldn't. An ActorReview might have flags for whether the actor was an understudy who took over a lead role or various other points specific to an Actor. Similarly, one might find a need for a ProductionPerforms table to carry some information specifc to the various performances of a production (e.g. weather, time of day, etc.) and, also, a PerformanceCast table that would carry the specisfics of the casting of a particular performance (e.g. who was cast in each part, who understudied each part, and finally, whether the primary or understudy played the part during that performance). with those, one might have a little better insight than could be gathered from the ProductionReviews entry alone. (For instance, if the review is based upon a performance in which some parts were played by an understudy instead of the primary actor, then the reviewer may not be presenting a true review of the prduction as "normally" cast.)
Your comment about OO design is interesting, especially since you seem to contrast it to good Relational Database Design. I have usually found that a good OO desing and a good relational design are not that different. Reusing code is one thing but reusing tables for multiple purposes tends to lead to compromises and kludges that lead to poor overall design. However, that is just "IMHO". 😉
January 21, 2008 at 8:25 pm
One thing I see as a potential problem is that logically you could have an 'ActorReview' and a 'ProductionReview' that share the same ReviewId which is not desired. In fact (thinking aloud here), that might just sway me to having all those seperate tables.
I was talking about my own approach (the first of the three I describe in my OP) 😉 - I see no problems with yours.
Your drill down description is spot on and the point of linking reviews of actors to productions makes total sense (as does reviews of productions being related to theatres). These relationships are already present in the tables that the reviews can refer to. A review of an Actor in a production could use the PK of the 'pivot' table between actors and productions; so the ActorReview table has ReviewId (FK/PK) and ProductionActorId (FK) and that is all. From that alone you could 'zoom out' to reviews of the production (or of the actor in other productions), then again to reviews of the show, etc. without any difficulty.
What happens when you realize that there is a need for a Set Design Review, a Reviewer Review (how I would love to do a couple of those ;-), a Theater/Theatre Review, a Choreography Review, and a Lyrics Review? Do you continue to add FK's ad nauseum?
I think you misunderstand me. I am talking about the first approach in my OP and not the second. The second approach has a single generic table with multiple, nullable FKs which is clearly horrid. The first approach has the generic table with no foreign keys to describe it's relation to what is being reviewed. This is handled by a single table per entity that has ReviewId as both FK and PK plus the FK for the entity being reviewed (and any extra fields specific to the particular review type). If I wanted to add reviews of musical numbers for example, I would just add a table like:
MusicalNumberReview
---------------------
ReviewId (FK / PK)
MusicalNumberId (FK)
I really am torn about using the generic table for the common properties of a review. In OO, I absolutley would have an abstract base class for a review (it would make no sense not to), but from what people are saying it might not be best in a relational db (this is what I mean by the differences).
Fortunately, this is a personal project and I have the time to agonise over such things!
Again, thank you for your time 🙂
Dominic
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply