How should I layout this database?
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:
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).
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:
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:
This is not tested, but I think it illustrates the point pretty well.
|All times are GMT. The time now is 12:36 PM.|
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0