[Resolved] commenting system (think amazon) -how to setup tables in DB?

[datagroove]

i have been trying to think through the best way to setup a db, i am new to php/mysql coding so maybe some of you can chime in and give your opinion. Perhaps this is ez and i have yet to read about it.

As it stands now i pretty sure i will need 1000+ tables. What i am asking here is what is the best way to implement a ratting system for comments.

the site:
1000+ pages of profiles (people, books, topics and organizations). Each page has a way to have users leave a comment in regard to the profile.

Of course some pages will have NO comments while others will have a couple but then there will be some that have 100's (or more).

users can post more then once on any given profile, but he/she can only rate each comment ONCE.

THINK: php.net commenting system BUT, with the ability to rate if the comment was helpful (amazon type rating: was this review helpful to you? yes/no [javascript])

The way i am imagining the db now is: each profile page has its own table. The table stores the userID, subject, time and text of the comment. (there is 1000+ tables there)

Where i am getting stuck is:
each user can only rate (yes/no) once per comment. How do i keep track of this ratting? Knowing that each potential comment rater (registered user) will have a userID, where do i store the users userID? One column in the same table as the commenting data, this column having all userIDs that rated it comma separated? (i know that is not good db design, comma separated data) or do i create a NEW table just for the comment ratings?

thanks for reading this, thanks for your help. as it stands now i fear i will need to implement a table not just for the profile/commenting but also for the comment rating. If so that is 2000+ tables.

thanks
chris

[nemonoman]

Please read up on database design. The whole point of a relational database is to establish basic entities, and then to populate those entites with specific records. The design you are describing is fundamentally unmanagable, and probably unbuildable.

You would for example set up a table:
"person".

You'd establish an id for reach person -- a simple unique number will do....this is why mySQL provides an autoincrement function.

Each person record
would include maybe, first name, last name, user handle, etc.

Then a table profile
Each profile record would have a unique id (this unique id is called a 'key'). You'd include also a profile type column: people, books, topics and organizations, etc

Then a table comments
Again each comment would have a unique identifier. It would also include references back to the PERSON who made comment and the profile associated with the comment. These references are called 'foriegn keys' ... keys related to other tables.
commentid
profileid
personid
commenttext

Then a table called commentrating. Get the idea?
ratingid
commmentid (Foreign key)
personid (Foreign key)
profileid (Foreign key)
rating

etc.

Your data is now stored in 4 tables, not thousands.

You can easily write SQL to answer these questions:
Which profiles have comments?
Which don't?
Which users have posted comments?
How many users have commented on profile 22?
What is the average rating of a comment?
Which user always rates comments higher?
Which profile has generated the most comments?
Which profile has comments which have generated the highest ratings?
Which user's comments have the highest rating?

Etc.

Isn't this the sort of data you'd like to extract?

Based on the sort of data structure I describe, you could write DYNAMIC SQL queries -- in one simple unchanging line --that would produce the answers to the questons above merely by changing a single variable (the record key you are looking for).

I DEFY YOU to achieve the same result with the thousands of tables structure you are describing.

What you want is thousands of RECORDS, not thousands of tables.

[datagroove]

thank you nemonoman

YES, i did go to bed last night thinking- what the heck am i talking about!! of course i do not need 1000's of tables just 2 new ones (comments and commentRatings)

thanks for your detailed reply.