0

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?

|improve this question
  • 1
    The reason to use separate tables because you ONLY have that few data makes no sense. One tagble is enough. And why habve the site name as string in every table? A separate "site" table and a foreign key would be proper. Host name and path in the host can be split. – TomTom Mar 24 '15 at 8:40
1

(Not an answer, but some pitfalls that make it difficult to design this for efficiency.)

  • More than half the users will have only one link.

  • Some users will each have over 100K links.

  • More than half the sites will have only one page.

  • Some sites will each have over 100K pages.

What does it mean? It means that any form of indexing, compression, etc, needs to be flexible enough to handle such extremes.

(I experimented with a similar experiment, quitting after a million or so links. Those are extrapolations of what I encountered.)

|improve this answer
  • Unlikely that anyone will only have one link (if that was the case they probably wouldn't have any use for my site), figure probably up to 10,000 on each of 60 sites (although I will probably limit to 1000 or 5000 max), average will probably be 500-1000 for each of 60 sites (could be up to 100 sites, which is why the separate tables for each site)... Imagine it as a cross between a click exchange, gallery and profile site. I know what I want is possible, because facebook, pinterest, and linkdin all exist. – krazykat1980 Mar 24 '15 at 7:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.