[SOLVED] Storing variable amounts of data in MySQL

[wpb]

Firstly, apologies if this topic has been covered before, but I've tried searching and I can't find much info. (Perhaps I just don't know the right terminology!)

 

I'm trying to create a database of products with associated reviews. Each product and its info will occupy one row in a table. What's the best way to store the reviews for each product, given that there might be a different number of reviews for each one? I thought a data structure like this was called a "ragged" table, but I'm not sure...

 

Would I have to reference a seperate "review product n" table from each nth row of my "master" table of products?

 

Any help very much appreciated!

[toplay]

Yeah, sample tables:

 

products table

-------------

product_id    (key)

product_name

etc.

 

 

reviews table

------------

review_id    (key)

product_id  (ties in to the products table to know what product the review is about)

review_text

 

 

Sample data:

 

product_id  product_name

----------  ----------------

1              Toshiba HD DVD

2              Sony Laptop

 

 

review_id  product_id  review_text

---------  ----------  -----------------------------

1              1                I liked the Toshiba DVD...

2              2                Sony's laptop was...

3              1                I didn't like this DVD player...

4              1                Over priced HD DVD....

5              2                Not bad Sony product....

 

 

Grabs all products and their associated reviews:

 

SELECT  *

  FROM  products p

  JOIN    reviews r  USING (product_id)

ORDER BY

            p.product_id ASC, r.review_id ASC

;

 

 

 

 

 

[wpb]

Thanks for getting back to me. That's what I suspected. So all tables in SQL have to be "rectangular", right?

 

Would it be necessary to somehow force SQL to store reviews grouped by product_id, so that retrieving say, all reviews for product #2 would not take too long? Is that even possible in SQL, or is the internal arrangement of the database data not something the user has any control over? I imagine in a database of 100,000s of products each with dozens of reviews, finding the right reviews for product n could take a bit of time, no?

 

Also, is there a simple way to find out _how many_ reviews there are of a particular product, without either keeping a running total in the products table, or just searching through every review in the reviews table?

 

Many thanks again for your help.

 

[fenway]

Thanks for getting back to me. That's what I suspected. So all tables in SQL have to be "rectangular", right?

I don't understand the analogy -- database tables shouldn't store repeated information in rows or columns; this is the primary concept behind normalization (see the sticky).

 

Would it be necessary to somehow force SQL to store reviews grouped by product_id, so that retrieving say, all reviews for product #2 would not take too long? Is that even possible in SQL, or is the internal arrangement of the database data not something the user has any control over? I imagine in a database of 100,000s of products each with dozens of reviews, finding the right reviews for product n could take a bit of time, no?

You have no control over how records are stored internally -- this shouldn't concern you.  If you add an index to the product_id column, running select * from reviews where product_id = 7 would be extremely fast.  And 100K rows is nothing for mysql.

 

Also, is there a simple way to find out _how many_ reviews there are of a particular product, without either keeping a running total in the products table, or just searching through every review in the reviews table?

Again, with an index, select count(*) from reviews where product_id = 7 will take a fraction of a second.  If you're paranoid, you can always create a  summary table, and have it updated via a trigger -- but that's overkill.