1

I'm wanting to create some simple website functionality through a little database idea I have in order to give myself the ability to create/retrieve customer reviews based upon tags/categories. More specifically, the reviews are not actually being input by the reviewer, they are being aggregated from outside sources and manually placed in the database by myself so that I can retrieve them at later dates and in specific site sections based upon the associated tags/categories that I place on them.

Listed below are the tables and their data structures that I've come up with:


The Rating Details Table would have at least the following fields:

**********************************************************************************
| rating_details_id   | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| professional_rating | INT          | UNSIGNED | NOT NULL |                |    |
| efficiency_rating   | INT          | UNSIGNED | NOT NULL |                |    |
| referral_rating     | INT          | UNSIGNED | NOT NULL |                |    |
| rating_comment      | TEXT         |          |          |                |    |
| rating_author       | VARCHAR(200) |          | NOT NULL |                |    |
| rating_date         | DATE         |          | NOT NULL |                |    |
**********************************************************************************

The Rating Tag Table – I'm assuming – would have the following fields:

**********************************************************************************
| rating_category_id   | BIGINT(20)  | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| rating_category_name | VARCHAR(75) |          |          |                |    |
**********************************************************************************

The final Main Rating Table – I'm assuming – would then consist of:

**********************************************************************************
| rating_id           | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | PK |
| rating_details_id   | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | FK |
| rating_category_id  | BIGINT(20)   | UNSIGNED | NOT NULL | AUTO_INCREMENT | FK |
**********************************************************************************

Question 1): Do these tables look correct for what I'm trying to accomplish? If not, please provide me with some feedback as to how I should structure these tables and their data.

Question 2): If these tables are correct could you provide me with some assistance as to how I might query these tables through joins so that I could add data and retrieve date?

  • Example 1) How would I query the DB to insert a new review where the categories are foo and foo2?
  • Example 2) How would I query the DB to retrieve all reviews where the category is foo?

I hope this all makes sense and I really appreciate any and all useful insight that you might be able to offer.

  • do you need a Ratings_Details table? It seems like the Ratings and Ratings_Details table are the same or could be in the same table. The question you need to ask is "Does a Rating have more than one Ratings Detail?". If the answer is "No", then you should have them in the same table. – blobbles Mar 19 '15 at 19:08
  • Hi @blobbles, thanks for the input. I wasn't completely sure if the Ratings_Details_Table and Main_Review_Table needed to be separated or not only because I'm unclear as to how I would appropriately associate any of the ratings with their corresponding tags/categories if I didn't have the Main_Review_Table join both Rating_Details_Table and the Review_Category_Tags_Table. Hopefully that makes sense. If you have a efficient way, please fee free to share, as I'm still learning a lot of the ropes and would love to have as much useful feedback as possible. – Corey Mar 19 '15 at 19:29
  • Oh right, I understand, probably just your table naming is a bit off. First off, decide whether you want to call it a Review or a Rating. It appears you are mixing terms here but it appears they are the same thing. Also, decide whether you want to call it Tags or Categories as it appears these are the same thing too. – blobbles Mar 19 '15 at 20:13
  • Once you have done this you should have 3 tables, 1 called Rating or Review, 1 called Category or Tag, and change the Main_Review_Table to Rating_Category (or Review_Category or the other combinations!). Then your design looks OK, but change the rating_details_id to just rating_id in the 2 tables they appear. Otherwise it looks OK, then we can start answering your question! – blobbles Mar 19 '15 at 20:16
  • @bobbles I'm using MySQL and I do have a question. Why would I change rating_details_id to just rating_id in the two tables? The reason I ask is because that would create a duplicate in the Main_Review_Table. Also, apologies on the confusion between my interchanging the use of the words review/rating, I've been using them interchangeably to mean the same thing because I'm assuming that what I'm doing is somewhat of a hybrid between the two because I'm wanting ratings that are 1-5, while also wanting feedback comments that you would typically see in a review. – Corey Mar 19 '15 at 21:01
1

The tables can be queried in a few different ways depending on what you want to do. If you wish to (Example 2) extract all the Reviews with a particular Category you can create a procedure like this:

SELECT review_id, professional_rating, efficiency_rating, referral_rating...
FROM Review r
INNER JOIN Review_Category rc ON r.review_id = rc.review_id
INNER JOIN Category c ON rc.category_id = c.category_id
WHERE c.review_category_name = 'foo'

For Example 1, if you want to insert a new review with 2 categories, you generally do this in 2 steps, handled by the application calling the database procedures. First insert the review with something like:

CREATE PROCEDURE Review_Insert (<review table values>, OUT NewReviewID bigint)
BEGIN 
    INSERT INTO Review (<column list>)
    VALUES (<value list>)

    SELECT NewReviewID = LAST_INSERT_ID()
END

You should then be storing the output of the NewReviewID in your application, which you then pass into the next procedure for associating the Review with the Category, something like:

CREATE PROCEDURE Review_Category_Insert (ReviewID BIGINT, CategoryID BIGINT)
BEGIN
    INSERT INTO Review_Category (review_id, category_id)
    VALUES (ReviewID, CategoryID)
END

This last one needs to be called for each Category that is associated with a Review.

You can do only one trip to the database too, if that is what you need, however it appears you could have 1 category associated with a review or 100 in one trip, which makes defining the parameters in your procedure difficult (not impossible, just difficult).

  • thanks for the input, I'm going to try this out and see how it works out with what I'm needing. – Corey Mar 20 '15 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.