SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    messing with my mind fristi's Avatar
    Join Date
    Feb 2009
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multi User Database design

    Hi,

    I'm planning to create my first big scale API. So far I have only done programming and designing on simple structures like a Blog or one person APIs.

    Now that i'm doing my first bigger scale design I came to a point where I would like to have some advice off the more experienced people.

    The API itself is specific to one thing only, no problem in design that. The doubts raise when I want to implement it for more users.
    Basicly there will be the homesite of my API:
    http://www.myapi.com

    On that page users will be able to sign up for the use of the api and own some small pages in the terms of:
    http://www.myapi.com/username


    Now so fare I see multiple possibilities but I don't know the right choice.

    1ste solution
    Create a new Database for every user who signs up.
    The API data itself will just be stored in every DB.

    2nd solution
    One Database with a more complex table structure.
    A User Table, A table with API data containing an ID to the user and a table specific table for every user.

    3th solution
    Basically the 2nd solution but change the specific table for every user to 1 table and every record has an ID to the user.



    So far I think the 3th is the worst because there is one table that will be huge!!! (if the API becomes popular) So I'm doubting between lots of DB's or lots of tables. Which are the reasons for which designs?

    thanks in advance
    To PHP or to Perl, that is the question!
    (Bucket - simpletest) User

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    personally, i would choose #3. by the time your site becomes HUGE(!!!) mysql will have finished development on partition pruning.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the 3rd solution is by far the best

    one table for each user will give you a nightmare if you ever need to change the table structure

    plus, getting aggregate statistics for all users would be disastrously inefficient
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    messing with my mind fristi's Avatar
    Join Date
    Feb 2009
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is this partition pruning you are talking about?

    (Are you sure that MySQL will keep in development, reading allot about the fact that SUN has been bought by Oracle and MySQL might be shut down )

    The site won't get slow because of the huge table? For instance searching the entire table for the data of a specific user?
    I'm just trying to foresee everything.

    Thanks again for your explanations
    To PHP or to Perl, that is the question!
    (Bucket - simpletest) User

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by fristi View Post
    ...MySQL might be shut down
    you been reading da wrong stuffs




    Quote Originally Posted by fristi View Post
    The site won't get slow because of the huge table?
    not if the table has been properly indexed, no

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    users
    -- id

    blogs
    -- user_id

    projects
    -- user_id

    bids
    -- user_id

    files
    -- user_id

    threads
    -- user_id

    posts
    -- user_id

    whatevers
    -- user_id

    blahs
    -- user_id


    ... I think you get the idea.
    The only code I hate more than my own is everyone else's.

  7. #7
    messing with my mind fristi's Avatar
    Join Date
    Feb 2009
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the advice people

    I can start programming now
    To PHP or to Perl, that is the question!
    (Bucket - simpletest) User


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
  •