How should I layout this database?

ETbyrne · 09-10-2008, 03:02 PM

I've been working on a social networking website that if things go well it could potentially get very large in the future so I've been thinking about how I should have the MySQL database(s) layed out. Here are my ideas.

(1) Have one database that stores all of the information in HUGE tables.

(2) Have two databases. One that stores information like user login details, front page articles, and other stuff like that. The Second one would be made up of separate tables for each user.

For example the user "evan" would have four (or more) tables in the database. Ex:

Code:

evan_comments
evan_photos
evan_albums
evan_groups

I'm looking for the best solution performance wise while keeping it as neat and scalable as possible. Any suggestions?

xenon · 09-10-2008, 08:49 PM

That's extremely bad for the server that processes the requests. A single MySQL table can work easily with as many as 1 billion rows. And that is alot of info. This way, it only has to deal with one physical file (opening it, reading it, etc.), instead of many table files (equal to the number of tables per user multiplied by the number of users).

buggabill · 09-11-2008, 06:16 PM

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.