Those were my thoughts exactly. Properly designed tables will scale WAY better than tables for each user.
I personally normalize most of my database stuff. In your example having a user table, an article table, a comments table, a photos table, an albums table, and a groups table would make the most sense. These could all contain the user id and/or the article id as indexes like the following:
tblUsers:
sql Code:
CREATE TABLE `tblUsers` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(50) NOT NULL,
`last_name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
)
tblArticles:
sql Code:
CREATE TABLE `tblArticles` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`article_name` VARCHAR(100) NOT NULL,
`article_text` TEXT NOT NULL,
`article_timestamp` INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
)
tblComments
sql Code:
CREATE TABLE `tblComments` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INTEGER UNSIGNED NOT NULL,
`article_id` INTEGER UNSIGNED NOT NULL,
`comment_text` TEXT NOT NULL,
`comment_timestamp` INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `UserIndex`(`user_id`),
INDEX `ArtIndex`(`article_id`)
)
More could be done with foreign keys.
If you wanted to get all the comments for an article with user names you could do something like the following:
sql Code:
SELECT tblComments.comment_text,
tblComments.comment_timestamp,
CONCAT(tblUsers.first_name, ' ', tblUsers.last_name) AS username,
FROM
tblArticles
JOIN
tblComments ON tblArticles.id=tblComments.article_id
JOIN
tblUsers ON tblComments.user_id=tblUsers.id
WHERE tblArticles.id=somearticleid;
This is not tested, but I think it illustrates the point pretty well.