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.