Table Reviews for other 3 Different Tables

Question

I have 3 tables:

Books (BookId PK, ...)

Posts (PostId PK, ...)

Users (UserId PK, ...)

And I need to create Reviews for all of them. Should I use a table Reviews:

Reviews (ReviewId PK, BookId FK, PostId FK, UserId FK)

And than with a Check Constrain make sure that only one of BookId, PostId or UserId is not NULL?

Or is a better way to do this?

No one can really answer your question without a better understanding of "create Reviews for all" and the system you are actually modelling. I suggest you stop cramming things together as you start your modelling process simply because you use the same name. At this point you should have 3 separate tables - BookReviews, PostReviews (whatever that is), UserReviews (whatever that is). Once you have defined all the attributes of each (and everything else about your system), you can then review your design for correctness and then for optimization. (cont.) — SMor, Oct 09 '20 at 18:29
Personally I can't imagine that a book review looks anything like a user review - but I have no idea what you are actually modelling and attempting to implement. Whatever you do, DO NOT prematurely optimize. That is simply wrong at this point in time and can easily lead you down many wrong paths. — SMor, Oct 09 '20 at 18:31
If reviews _are_ similar, e.g. they all have `ReviewId`, `PostedByUserId`, `PostedDateTime`, `Rating1To5` and `Comment`, then putting them all in a single table _may_ make sense. You could use three tables to relate them to their targets, e.g. `BookReviews` with `BookId` and `ReviewId`; `PostReviews` ... . An assortment of constraints/triggers can avoid issues like one review applying to 3 books (one of them twice) and a user. This is a good opportunity to use stored procedures to handle operations properly, e.g. `AddBookReview`, and denying users write access to the tables. — HABO, Oct 09 '20 at 19:01
@SMor to specific problem I see 3 options: 1. Add tables BookReviews, PostReviews and UserReviews. This is the solution I usually choose; But can't control that one Review can be only for one Book, Post or User. 2. Add BookId, PostId and UserId with Check Constraint as in answer. Feels strange to me as if I get more tables needing reviews I would need to add more columns. 3. Make Reviews a base table for Users, Posts and Books with a 1 to 1 relationship. Then BookId, PostId and UserId would be both a FK and PK and its value the same as ReviewId. Also strange in this case. — Miguel Moura, Oct 09 '20 at 21:26
@HABO Yes, I am considering using 3 tables: PostReviews, BooksReviews and UsersReviews — Miguel Moura, Oct 09 '20 at 21:26
If you add the attributes for each type of review into your schema, that might make things more clear to you. But again, I still do not see what "review" means in the context of user or post nor why you would have a completely separate table for a review. Will multiple books have the same "review"? This sounds very similar to [one description table to rule them all](https://softwareengineering.stackexchange.com/questions/58372/is-the-one-description-table-to-rule-them-all-approach-good). — SMor, Oct 09 '20 at 22:33
@SMor Consider Reviews table with 3 columns (note: in fact it will have around 12): ReviewId, ReviewAuthorUserId and ReviewText. For example, a Book can only have one Review per User. I am using a Reviews table to have all reviews in the same table. Then I just need a way to register for each object type (User, Book, Post, etc) the review is for. — Miguel Moura, Oct 09 '20 at 22:43

score 1 · Answer 1 · answered Oct 09 '20 at 18:09

Here is one approach that counts non-null value in the three columns and sets the constraints on the result:

create table reviews (
    reviewid int primary key,
    bookid int references books(bookid),
    postid int references posts(postid),
    userid int references users(userid),
    check (
        case when bookid is null then 0 else 1 end
        + case when postid is null then 0 else 1 end
        + case when userid is null then 0 else 1 end
        = 1
    )
)

This requires one (and only one) non-null values in the three foreign keys. If you want to allow all three columns to be null, then use <= 1 instead of = 1.

Table Reviews for other 3 Different Tables

1 Answers1