Database relationship to many tables and presentation in entity structure
2 parts of the question:
first What is the best way to set up the structure of tables and relationships given the following scenario: I have many tables that store different kinds of data (e.g. books, movies, magazine - each as different tables) and then one table that stores reviews that can link to any of the table types. Thus, a row in the overview table can refer to a book or magazine table.
Now I have a 3rd table that defines the tables available and gives them an ID number. As a result, the lack of true relationships that are stored in Book Reviews ends. Is this the best way to do it?
second How to represent fake relationship in Entity Framework? I can make a query that would join 3 tables, but is there a way to simulate it in a table mapping?
source to share
Another way to think about this is to treat BOOKS, FILMS, MAGAZINES as subtypes of REVIEWABLE_ITEMS. They probably share some characteristics in common - without knowing more about your problem area, it would be difficult to be sure. The advantage of this approach is that you can model REVIEWS as a dependency on REVIEWABLE_ITEMS, giving you both a single table for Reviews and a forced relationship.
change
Yes, this is similar to type expansion in the OO paradigm. You're not saying what flavor of the database you're going to use, but in this article, Joe Celko shows you how to do it in SQL Server. The exact same implementation works in Oracle and I expect it to work in most other RDBMS products as well.
source to share
It really depends on how you want to access / view the reviews.
I would do one table for each type of disclosure: one for books, one for movies, etc. with a one-to-many relationship for each one (between books and book reviews, movies and movie views, etc.) If you need all reviews in one table, create a view that will select all reviews using UNION ALL ...
source to share
Either you include the concept of "review", which can be either a book or a magazine or ... and to which "reviews" can refer, or you can have the concept of "review", which can be either a "book review", which can refer to a book or a "magazine review" which may refer to a magazine, or a "newspaper review" which may refer to a newspaper, ...
Since there are no truly relational systems, you cannot do without highlighting one of these two abstractions in your database design. (If possible, if you want to implement a lot of startup code.)
source to share