SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Most efficient way of storing data from multiple accounts

    Hi,

    As part of a system I am putting together I need to allow users to create thier own accounts on my servers. Each user can create their own account, and then have their users register for it. Each account needs it data seperate from the others, a member registered for one account should not be able to view another account and a username registered with one account should still be available to the other accounts.

    The ways I have been looking at are:

    1. Create a new database for every account created so that all users are kept in seperate databases.

    2. Have one table for users, one for topics, one for posts etc and then associate each row within this table with the relevent account. So for example a user could register with the forum with the ID 4, so their user entry would be

    Userid: 234
    Username: xxxx
    Password: xxxx
    Forumid: 4

    Then when a new member registers with any account I simply check that there is not another user with the same account ID and username. Indexes on relevent fields in this system could help speed up huge tables.

    I expect to quickly have 20,000 plus accounts (and in theory it could go up to hundreds of thousands).

    I guess my question is which of these methods is better from a speed point of view once we get a large number of accounts and users. Also, are there restrictions on the number of fields in a table that could cause problems?

    Using MySQL by the way, on an Apache server.

    Thanks a lot for any suggestions.
    Last edited by grandad; Aug 14, 2007 at 03:50.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'd create a separate database per forum. It'll be faster and easier to scale as you start using multiple servers. And won't require rewriting the entire forum software and re-tuning every query to use your new multi-column indexes that include the forum IDs.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •