One Table or Many?

[Grant Nosbush]

I have a question maybe someone could offer some insight to. I am
currently writing a messaging program that utilizes mysql. I am
debating between the following two situations. Have one table and store
all the messages in there and have a column for user ids. Second, make
a table for each user and store each users' messages in his or her
respective table. I'm thinking the second situation because it would
reduce the file size of one big table. What do you think? Is there a
limit to the size of a table? What about a limit to the amount of
tables one can have in one database? My guess is this program will have
200 users on it, however, I want to have it support many more if needed.
Any input on the matter would be greatly appreciated! Thanks in advance!

[Marco]

Grant Nosbush wrote:

Have one table and store all the messages in there and have
a column for user ids. Second, make a table for each user and store each users'
messages in his or her respective table.

Personally, I'd store all the messages in one table, and all
the users in another:

CREATE TABLE Message (
author SMALLINT UNSIGNED NOT NULL, /* foreign key */
when TIMESTAMP NULL, /* when msg was posted */
title VARCHAR(80) NOT NULL, /* title of message */
body TEXT NOT NULL, /* the message itself */
PRIMARY KEY (author, when),
INDEX IMessageWhen (when) /* for ordering by date */
);

/* fixed/static table for faster access */
CREATE TABLE User (
id SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
joined DATETIME NOT NULL, /* when the user joined */
firstName CHAR(15) NOT NULL,
lastName CHAR(20) NOT NULL DEFAULT "Smith"
);

Yes, the Message table will get big. But what kind of
SELECTs do you perceive doing? If all messages are in
separate tables, per user, then you'll have to cancel
out Cartesion joins with a carefully written WHERE clause
that eliminates unwanted permutations to avoid a massive
resultset.

Always think "how will I access the records? What kind of
SELECTs will I use?" That's just my opinion. I'm not a
database expert by any means!

Marco
----------------------------------------------------
Please remove digits from e-mail address (tr/0-9//d)