A reviews database. Am I on the right track?

ade234uk

There is something I have never ever worked out how to do and this is a simple review system for a tutorials website.

Let’s say I have a table that holds the tutorials

///// tutorials table /////
tutorial_id
tutorial_subject
tutorial_ text

The table holds all my tutorials. Let’s say I have 20 tutorials in total. Each tutorial has its own unique id number.
I want to allow users to be able to write a review about any tutorial.

So the simplest way and most wrong way of doing it would be to do the following.

///// tutorials table /////
tutorial id
tutorial_subject
tutorial_ text
review1
review2
review3
review4
review5

This way each tutorial could hold up to 5 reviews. However what happens if one tutorial only has two reviews and an another tutorial has 20 reviews how would i do this?

I suppose one thing I could do is just create a table for reviews. Each review has it own unique id number.

//// reviews table ///
id
tutorial_id
review

When a review is submitted, it gets a unique id number, but also inserts the tutorial_id in to the database.

Am I on the right track? How would I join the tables together?

rincewind456

You'd join them on the tutorial_id as that is the common field in both tables.

ade234uk

I have never ever used the join command before in mysql syntax. What is difference between using a join command and doing something like the following

select from reviews_table where tutorial_id=x

TobyRT

It's basically the same thing:

Your whole query (assuming you're displaying the tutorial and review on the same page) would be along the lines of

"SELECT
t.tutorial_id,
t.tutorial_subject,
t.tutorial_text,
r.fk_tutorial_id,
r.review
FROM
tutorials t,
reviews r
WHERE
tutorial_id = '$id'
AND
fk_tutorial_id = '$id'"

I've renamed your tutorial_id field in the reviews table as it's exactly the same name as the field in the tutorials table. Putting fk_ as a prefix means you know it's the foreign key linking to the other table.

$id you would have done by $_GET-ing the id from the link to view that page.

bpat1434

Actually using Joins can be easier. Lots of times it's nicer to be more explicit about which info you're getting from where and how. A good example of a more structured Join:

SELECT t.tutorial_id, t.tutorial_subject, t.tutorial_text,
    r.tutorial_review
FROM tutorials AS t
INNER JOIN reviews AS r
    USING(tutorial_id)
--    ON t.tutorial_id = r.tutorial_id -- This is commented out, you can use
-- either syntax to complete the join.  It was just there for informational purposes

That's a little more explicit as to which table is being joined to which table and exactly how it's being joined. It is extremely helpful to use this syntax rather than the short form (see post above) because once you start joining 3, 4, 5 tables, you'll get lost. Just a suggestion.