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.
Best Answer
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:
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:
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:
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).