opinion: use one table or multiple to store data

drew010

I am writing a tag board type program where people can sign up for a remotely hosted board w/ my site... I am wondering if for the data table, where posts are stored for each users board, if I should use one table and do a SELECT FROM messages WHERE uid = 'x'; or make a table for each user and do SELECT FROM $uid_messages;

The first would take up less room but with a lot of users may be very slow and the second of course will be fast but if I have 100 users, i will have 100 data tables..

Which should I do, one table or multiple???

lanjoky

I would probably do 1 table just to keep it all together ... if u get like 100 users or more you would have in my opinion too many tables, doing it the other way, and in the process make things harder to debug if something crashes ... thats my $.02 ...

leatherback

Hi,

If you are talking 100 users.. Querying will be a snap: What is 1/100th of a second when people are getting their page in through a telephone line.

It is easier to manage for you when you put it in one table.

drew010

The thing I am worried about is::

Say there is 100 users, each have 45 messages, that is 4500 values in the table, and doing a where clause on that may be slow or take time.. Especially if there is more users and more messages in their board. Because the queries will be very frequent.. If each of the 100 board owners gets five pageviews per minute on a page w/ a tag board.. That is going to run that query a little over 8 times a minute, and then in a real scenario people will have more pageviews than that per minute. Seems like the mysql load get very big and i will have lots of heavy incoming requests to handle.

kevinsequeira

hm, id still go with one table ..

reg
kevin

leatherback

Well,
IF you are going to have 100 board and
IF they get 5 views per minute
Then you will end up with WAY more then 45 records per board, I'd say.
But I am not sure wether that is a realistic number!? And then again..
I do not think that speed will be a problem: I am currently testing import routines from 20 flat-ASCII files each holding appr. 600 rows, with each 4 values. I extract the data from the files, create graphs, output it to screen, and add some info from a database. And the page loads in litt. miliseconds. Locally it is a flash of my screen, so to speak.

In an earlier post of mine (+1.000.000 records, how would YOU do it) I had a reply of somebody working a database with 500.000 records, and he had no real speed problems..