creating many tables in one database

[amedhussaini]

what are some theoretical issues I should be aware of (as a relatively newbish PHP/mysql programmer) regarding tables?

 

for instance..

 

1.  suppose i'm developing a typical web 2.0 site which relies on user content.  users create specific things and then they're displayed, and other users act on these creations.  is it feasible to create a new table each time a user creates this "thing"?  as it stands right now, each new "thing" is just another line in a table, but i now have to parse "text fields" in order to see what other users have acted on this field.  it would be a lot easier if i could just create a new table for each item, and then add rows to these tables to represent the different users acting on these "objects".

 

so, i know its possible to do it this way, but what are the issues regarding performance?  if a site grew rapidly to tens of thousands of users, would a database with tens of thousands+ of tables bog down the system?

 

btw, great forum.  this is my first post, i'm glad i found you knowledgeable folk.

 

 

amed

[moon 111]

Suppose you are creating a news system. An admin can create a news item and others can post comments for that artice. I can create a new row for the news item when it is created an then a table for the comments. A much better way, however, is to have a table with news items and create a new row when one is create and supply an id. Then every time someone adds a comment you create a new row in comments with the id of the comment your reffering to.

 

The database could look like some like this:

 

[/td]News

IDTitleMessage

1A titleThis is a message

2A second titleThis is another message

 

Comments

News IDTitleMessage

11This comment reffers to the first articleBecause News ID is 1

21This comment reffers to the first article tooBecause News ID is 1

32This comment reffers to the second articleBecause News ID is 2

 

Get it?

[amedhussaini]

i think i get it.

 

as the message table increases to larger and larger sizes, and the comments table increases to larger and larger sizes, running searches to pull which comments were written by specific people for specific threads.. this becomes difficult?  or will the use of indexes help this later on?