mySQL Database Creation/Design - Not Sure How to Organize Data When it's Like This

Question

Normally I can simply search google with a question and find an answer, but I know literally not a single thing about mySQL databases, so I'm starting from scratch here.

My question is how to format/organize data that I have, when it isn't as simple as a spreadsheet. The way I thought it would be formatted, would result in hundreds of thousands of individual tables, which doesn't seem right. Here is the data I have, and maybe somebody could point me in the right direction as to how to organize it in a mySQL database so that it's not only organized, but so it would be possible to generate reports based on each of the individual "points" of data:

I have scraped product reviews from specific URLs from a website, lets say Amazon for examples sake. I need to keep the reviews in the order they were left on Amazon.

To keep this simple, I'll say I've scraped product reviews from three different URLs on Amazon:

http://amazon.com/product/12345-x
http://amazon.com/product/12345-y
http://amazon.com/product/12345-z

So from each of those three URLs, there were 3 different people on each URL that left a review. So how I thought it would be stored in a database (which can't be right) would be like this:

          TABLE FOR PRODUCT 12345-X
REVIEW NUMBER    REVIEWER     THEIR REVIEW     NAME OF PRODUCT   PRODUCT PRICE   REVIEW LEFT ON
1                username     great product    some product      $399            monday
2                username     crappy product                                     wednesday
3                username     okay product                                       wednesday


          TABLE FOR PRODUCT 12345-Y
REVIEW NUMBER    REVIEWER     THEIR REVIEW     NAME OF PRODUCT   PRODUCT PRICE   REVIEW LEFT ON ON
1                username     great product    some product      $399            monday
2                username     crappy product                                     wednesday
3                username     okay product                                       wednesday


          TABLE FOR PRODUCT 12345-Z
REVIEW NUMBER    REVIEWER     THEIR REVIEW     NAME OF PRODUCT   PRODUCT PRICE   REVIEW LEFT ON ON
1                username     great product    some product      $399            monday
2                username     crappy product                                     wednesday
3                username     okay product                                       wednesday

Now since I've got the data for thousands of products, that would obviously result in thousands of tables, and that can't be the right/best way to organize things.

To make things worse/more complicated, sometimes the same product has it's price changed, so it's sold at two different prices (actually, lots of different prices). I'd like to also be able to get an "average price" of each "same product" that's been sold at more than one price, as well as display what day reviews are left most often as a whole & on a per product basis.

So you can see that I need to be able to generate reports not only on each product, but for then each "column" of data within each product...which would result in even more tables.

Can somebody point me in the right direction in how this data should be organized in a database?

I apologize if what I'm asking is too ridiculous to answer, I just don't have even a clue as to how to organize this in a database, or even where to start (what phrase to type into google).

Answer 1

if I got you right you want to store threr diffent things: 1 Products 2 Reviews for each product 3 Data of the person how rowted the review like name ...

so all you need are ther tables.

TABLE FOR PRODUCT
ProductID  P_Price   P_Name
1          399       Name X
2          299       Name y
3          199       Name z

TABLE FOR Review
ReviewID  ProductID  Review            R_Date           UserID
1         2          Review for y      1/1/2012         1
2         1          Review for x      1/1/2010         2
3         3          Review for z      4/4/2009         2


Table for User
UserID    U_Name
1         Peter
2         Simon

So now you can e.g. see that Simon wrote a Rewiev for X and Z. And that each product has one review. In general, you do someting realy worng if you have to create a table during runtime. The Tabes are always a fix struktur that is only changed if you decide make some big changes, like adding a Password to each user. When u add a new information like a new review, u always only add one ore more entrys.

I thing you should get a book or a long tutorial about MySQL (or wath ever you use) where hopfuly is a big chapter about data organisation.

I hope I could help you.