I am doing a social network and came up with the following concern regarding database storage. Let's put facebook as an example, imagine 2,000 photos per user, just 1,000 users, it would equal to 2,000,000 rows in total assuming there is a row per photo.
Now imagine 100,000 users it would be two billion rows (in a single table?)
The problem is how to handle this? With clones of the same table and then selecting by user id which is assigned to one sector?
photos_0 photo, date photos_1 photo, date
and so on, then storing the suffix, 0, 1, etc along the user id and working from that point.
Is there any other better known solution?
//media mysql_query("DROP TABLE IF exists media"); $vals='sbid int NOT NULL AUTO_INCREMENT, PRIMARY KEY(sbid), vids varchar(200), vidshd varchar(200), vidso varchar(200), duration varchar(200), videow varchar(200), videoh varchar(200), notify varchar(200), pics varchar(200), picss varchar(200), picsm varchar(200), picsr varchar(200), picsa varchar(200), faces longtext, descr longtext, location varchar(200), sbidv varchar(200), id varchar(200), albumid varchar(200), albumn varchar(200), norder int(200), oldorder int(200), title varchar(200), shot varchar(200), shott varchar(200), nye varchar(200), visibility varchar(200), datetimep int(200), datetimep_pp int(200)'; $vals=str_replace(" "," ",$vals); $predefined=''; mysql_query("CREATE TABLE media ($vals) AUTO_INCREMENT=10000000;");
The above is the sample table, would I go with storing up to billions of records on it, then updating and selecting from it normally where id='$uid' and $uid=theuser's id.
It is not the final table structure but I think it wouldn't be possible to use one table only so I thought about cloning and then when altering a table the need to alter all of the tables becomes necessary which sounds difficult to have to alter hundreds or thousands of tables using lots of queries whereas if it were one table only then it would mean a single query, that is one of the concerns I have about cloning but I also don't know if it is the actual solution to such eventual problem.