SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Many tables or many DataBase?

    Hi,

    I would like to know if it's better in terms of performance to have many table in 1 database or if it's better to have many DataBase with few tables....

    Thanks

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1 database. Many tables

  3. #3
    SitePoint Member
    Join Date
    Jan 2006
    Location
    America
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by afrika
    1 database. Many tables
    I second that. Create 1 database with a lot of tables.

  4. #4
    SitePoint Enthusiast Aaroniza's Avatar
    Join Date
    Dec 2005
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I only have 3 databases so , few databases many tables.

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you have a good naming convention for your objects, one database gives you much more flexibility, with centralized accounting, reconcilliation , security etc

    ...and many other issues

  6. #6
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess it depends on what you are doing.

    For a typical website, such as sitepoint, i see no need for more than 1 database.

    tables named like:
    sitepoint_articles_xxxxxxx
    sitepoint_forums_xxxxxxx

    and so on, easily seperate it out, and make it easy for making the whole site dynamic.

    In an enterprise situation though, It might be best to split it out into seperate DB's
    At my office, we generally set up 1 database per client. But, these aren't web sites. these are databases of client info, products, or whatever, that would NEVER be joined to another company.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by builder
    I would like to know if it's better in terms of performance to have many table in 1 database or if it's better to have many DataBase with few tables....
    neither

    the question about performance has no meaning when you consider only the containers

    performance of what?

    if the question is, how to return data from one table, it makes no difference whether you have many tables in one database or many databases with few tables

    so the real question is, what do you want to do?

    if you want to return data from all tables, that's a differnt question, isn't it?

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

  8. #8
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    >>so the real question is, what do you want to do?

    Yes Sir

    I used to have that mentality, when i first to into dataware housing, thinking that due to the heavy load, its best to spread it accross databases.

    ADVICE: Get a good naming convention for your objects

    accounting_Financials_PO
    users_Admin_users
    users_login_history

    etc, a hierachy of objects, clearly defined


  9. #9
    SitePoint Evangelist
    Join Date
    Aug 2004
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    hierachy of objects

    hierachy of objects is important.

    however i limit the use of underscore as usually thats notation for foreign keys or many to many relationships

    example:
    2 tables: user, permission
    to join with many to many i use : user_permission

    I also try to show which tables are related together by:
    Users, UsersPermission
    Users_UsersPermission

    ----------------
    I try to use the pascal notation: PascalNotation for table names
    for column names I use the camelCase


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
  •