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:
I'm looking for the best solution performance wise while keeping it as neat and scalable as possible. Any suggestions?
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).
I have optimistic thoughts, even though sometimes (if not always) life's a bitch.
The Following User Says Thank You to xenon For This Useful Post:
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:
If you wanted to get all the comments for an article with user names you could do something like the following:
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.
"Why is it drug addicts and computer aficionados are both called users?" -Clifford Stoll
The Following User Says Thank You to buggabill For This Useful Post: