database relationship to many tables and representation in entity framework

Question

2 part question:

1st What is the best way to setup a table/relationship structure given the following scenario: I have many tables that store different kinds of data (ie: books, movies, magazine - each as different tables) and then one table that stores reviews that can link to any of the table types. So a row in the review table can link to either books or magazines table.

How I have it now is that there is a 3rd table that defines the available tables and gives them an ID number. There ends up being no true relationship stored that goes from Reviews to Books. Is this the best way to do this?

2nd How do I represent the fake relationship in Entity Framework? I can do a query that would join the 3 tables, but is there a way to model it in the table mapping instead?

Answer 1

It really depends on how you want to access/view the reviews.

I would implement one table for each kind of revew: one for books, one for movies, etc. with a one-to-many relationship for each of them (between books and books reviews, movies and movie reviews, etc). If you need all the reviews in one table, create a view which selects all reviews with a UNION ALL.

Answer 2

The other way to think of it is to consider BOOKS, MOVIES, MAGAZINES as sub-types of REVIEWABLE_ITEMS. They probably share some common characteristics - without knowing more about your problem domain it would be hard to be sure. The advantage of this approach is that you can model REVIEWS as a dependency of REVIEWABLE_ITEMS, giving you both a single table for Reviews and an enforceable relationship.

edit

Yes, this is just like extending types in the OO paradigm. You don't say which flavour of database you're intending to use but this article by Joe Celko shows how to do it in SQL Server. The exact same implementation works in Oracle, and I expect it would work in most other RDBMS products too.

Answer 3

Either you include a concept "reviewable", which can be either a book or a magazine or ... , and to which "reviews" can refer, or else you can have a concept "review", which can either be a "book review" that can reference a book, or a "magazine review" that can reference a magazine, or a "newspaper review" that can reference a newspaper, ...

Because truly relational systems do not exist, you cannot do without explicitizing one of those two abstractions in your database design. (Unless perhaps if you are willing to implement a lot of trigger code.)