SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    One DB for multiple sites?

    Hey,
    I currently have a 1&1 Business package, which is wonderful except for the fact that is has only one 100mb db. I host bout 5 sites on the same db... is the bad, and should I consider upgrading?'

    How many sites is it OK to host on 1 db, and why?

    Thanks.

  2. #2
    SitePoint Zealot CodeLes's Avatar
    Join Date
    Jan 2005
    Location
    Oklahoma
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is an interesting question because I find myself creating a new DB for everysite even if it just needs on table for basic data....
    "If you were plowing a field, which would you rather use:
    2 strong oxen or 1024 chickens?"
    Seymour Cray

    Learn to code if you must, but first learn to solve problems.

  3. #3
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is an interesting question because I find myself creating a new DB for everysite even if it just needs on table for basic data....
    When I had a cheap cPanel account with Geekhosting, I used to do that. 1&1 is not up to par with the industry in this regard, and it's really weird. I used to create DB's for guestbook scripts, but now I'm stuck with just one.

  4. #4
    SitePoint Addict flyingpylon's Avatar
    Join Date
    Mar 2002
    Location
    Fishers, IN USA
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have an account where there is one SQL Server DB included in the plan. I just name my tables and stored procedures with a 2-3 character prefix that indicates which site it's for. No biggie. If you didn't want to duplicate commonly used tables, you could add a Sites table to the DB and include a SiteID foreign key in each table that required it. Again, it's not a big deal.

    There is no simple rule for the number of sites that can use a single DB, it all depends on the amount of content, the type of queries used and the frequency of requests for those queries.

  5. #5
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm. Thanks for the info. I may be fine, then, as I'm using good prefix structure.

  6. #6
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The major issue here is probably legal, and so since most freelancers don't really care about legal issues won't be bothering many readers here...

    But lets say you fall out with ClientA, and they demand all code & content to be returned, so they can pass it on to the poor monkeys who will be responsible from now on.

    How do you do that cleanly if you have mixed different customer's data in one table? Not easily....

    So I would suggest, in descending order of preference:

    Freedom of choice:
    a) Each application: one database.

    Limited by cost?
    b) Each Client, one database, several applications.

    Shared database a must?
    c) Each application one table (object) prefix, such as
    clientA_Products
    clientA_Customers

    Do not do this:
    d) A total last resort: A base table: "Clients" with the client as the table's PK referenced by every other table as an FK, with the client FK being used as part of every table's PK.

    HTH

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't understand what's wrong with d) from a legal point of view

    the client wants his data, you give it to him

    this leaves all the other clients in the table, and you still have the code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I was mumbling.
    There is nothing wrong with D from a legal point of view.

    If such a situation should arise, however, you would have to:
    a) copy the database to a new location
    b) delete from *everytable* where clientid <> MyClientID

    which seems like more work than just copying the correct table, or just copy the correct database.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i still don't see what's so hard about it

    you have several tables with data from multiple clilents

    client A wants to take his data and go home

    so you run a SELECT for each table with WHERE clientid='A'

    then you run a DELETE for each table with WHERE clientid='A'

    what's all this about copying databases?

    let me ask you this -- do you think amazon.com has a separate table for every person who has registered a wish list?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i still don't see what's so hard about it
    Not hard, just more work.
    Quote Originally Posted by r937
    you have several tables with data from multiple clilents

    client A wants to take his data and go home

    so you run a SELECT for each table with WHERE clientid='A'

    then you run a DELETE for each table with WHERE clientid='A'

    what's all this about copying databases?
    This gives you the data, not the database.
    Quote Originally Posted by r937
    let me ask you this -- do you think amazon.com has a separate table for every person who has registered a wish list?
    That would be ridiculous. Why do you ask?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    exactly

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

  12. #12
    SitePoint Evangelist
    Join Date
    Oct 2003
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, thanks for the info, everyone. The thing is that I'm... uhh... 14 and doing some non-contracted work (hey, next time I'll try to write up a good contract, but I'm not a lawyer). It's for a paintball team, not a db-reliant business. The max number of DB's I could get without upgrading to reseller for $20 more/month is 3. I may have to share those.

    My prefix structure is somewhat like this (not using the actual prefixes):

    site1_blahblah
    site2_something
    site3_asdlkfjdsf
    site4_phpbbtableshere

    Shouldn't that be fine?

  13. #13
    SitePoint Addict flyingpylon's Avatar
    Join Date
    Mar 2002
    Location
    Fishers, IN USA
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, that should be fine.

  14. #14
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    va
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    skyraider,
    One DB for multiple sites:
    1. Security - if someone hacks into your database, they can destroy ALL your site data.
    2. Maintainability - difficult to maintain all the tables since they are all in one database. One way to get around this is to prefix each table with the site name. Remember, you also have to maintain stored procedures and triggers. It's difficult to distinguish one site's store procedures or triggers from another.
    3. Performance - it might also affect the database performance since you have multiple sites connecting to one DB.
    http://www.GeekGig.com - Resources for the Geeks!


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
  •