I'm looking at making a site that will potentially have up to a million users, and each user will have hundreds/thousands of links connected to a set of sites, as well as other info. I'm using mysql and was thinking of setting up the database with tables for each site in the set along with tables for the other info...
Table 1
-----------------------------
|UserID | Links for site 1|
-----------------------------
512 | www.site1.com/4564
512 | www.site1.com/1298
512 | www.site1.com/09312
512 | www.site1.com/126543
512 | www.site1.com/231
6143 | www.site1.com/32
6143 | www.site1.com/566478
Table 2
-----------------------------
|UserID | Links for site 2|
-----------------------------
512 | www.site2.com/click/45564
512 | www.site2.com/click/898
512 | www.site2.com/click/7312
512 | www.site2.com/click/56543
512 | www.site2.com/click/4331
6143 | www.site2.com/click/9832
6143 | www.site2.com/click/78
.......
Table 20
-----------------------------
|UserID | Links for site 20|
-----------------------------
512 | www.site20.com/564
512 | www.site20.com/125498
512 | www.site20.com/479312
512 | www.site20.com/6543
512 | www.site20.com/2713
6143 | www.site20.com/32346
6143 | www.site20.com/6478
I probably won't be storing the whole link, just the bit on the end and then rebuilding the link before I display it... unless storing the whole link makes my site load faster.
Then of course:
-----------------------------
|UserID | referral links |
-----------------------------
512 | www.site1.com/join/4564
512 | www.site2.com/register/1298
512 | www.site3.com/register/09312
512 | www.site4.com/join/126543
512 | www.site5.com/referral/231
6143 | www.site1.com/join/32
6143 | www.site2.com/register/566478
And
-----------------------------
|UserID | Profile Links |
-----------------------------
512 | www.site1.com/profile/4564
512 | www.site2.com/user/1298
512 | www.site3.com/user/09312
512 | www.site4.com/profile/126543
512 | www.site5.com/user/231
6143 | www.site1.com/profile/32
6143 | www.site2.com/user/566478
And a few more for wishlists, About Me, Profile Pic, etc.
My question is, is this the most efficient way to do something like this? Or would it be better to use 1 row for all of user 512
's site1.com
links, and 1 row for 512
's site2.com
links, etc, since I'll usually be pulling all of them almost every time I pull any of them? If so, how would I do that?
Please explain your answers well, and links to more info would be appreciated, since I'm a complete newbie to databases.
Thanks!
More info...
The average user (95%) will probably have 500-10,000 links in each table (if I don't decide to limit it) and there will be probably between 60-100 site tables. I don't see ever having over a million users, and I'll probably be lucky to have 20,000, but better to plan ahead just in case.
Not all users will have links for all sites.
Data usage-
Most of the time I'll be doing something along the lines of:
Display all site3.com
links for user 512.
I'd also like to be able to display a random set of (for example) all site5.com
links.
I assume this means indexing both columns of my table?