Page 8 of 8 FirstFirst ... 45678
Results 71 to 76 of 76

Thread: SQL layout

  1. #71
    Senior Member Mirviriam's Avatar
    Join Date
    Apr 2008
    Location
    Where the moon cuts the wind.
    Posts
    259
    Downloads
    4
    Uploads
    0
    Yea, we probably won't see any drag on these databases...sql is very slick it's only when you get tons of tables & millions of entries that the over head gets to the server.

    I was told by the DBA's at work, that for a low population game (less than 500 users per week on birthright.net?) & low intensity use (turn based game) we'd never push the server no matter how badly we ignored optimizing.

    It was more that generating tables at each new creation would make the database's diverge drastically.
    Legacy of Kings: Member

  2. #72
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,373
    Downloads
    48
    Uploads
    1
    here are some statistics from last month:
    8,859 Visits
    Previous: 8,529 (3.87%)

    4,756 Absolute Unique Visitors
    Previous: 4,733 (0.49%)

    53,665 Pageviews
    Previous: 55,330 (-3.01%)

    6.06 Average Pageviews
    Previous: 6.49 (-6.62%)

    00:06:31 Time on Site
    Previous: 00:06:37 (-1.67%)

    47.91% Bounce Rate
    Previous: 45.81% (4.58%)

    47.97% New Visits
    Previous: 50.09% (-4.22%)


    even though search engines take up 60% of the bandwidth (and DB requests) the site still has no trouble.
    Te audire non possum. Musa sapientum fixa est in aure.

  3. #73
    Senior Member Mirviriam's Avatar
    Join Date
    Apr 2008
    Location
    Where the moon cuts the wind.
    Posts
    259
    Downloads
    4
    Uploads
    0
    Quote Originally Posted by Arjan View Post
    here are some statistics from last month:
    8,859 Visits
    Previous: 8,529 (3.87%)

    4,756 Absolute Unique Visitors
    Previous: 4,733 (0.49%)

    53,665 Pageviews
    Previous: 55,330 (-3.01%)

    6.06 Average Pageviews
    Previous: 6.49 (-6.62%)

    00:06:31 Time on Site
    Previous: 00:06:37 (-1.67%)

    47.91% Bounce Rate
    Previous: 45.81% (4.58%)

    47.97% New Visits
    Previous: 50.09% (-4.22%)


    even though search engines take up 60% of the bandwidth (and DB requests) the site still has no trouble.
    Just so we're clear...you're basically saying the server is a rock & 4 times more powerful than I claimed. Which means you feel we should keep the standard tables? Or does it mean you think we should generate a set of database tables for every game? Or do you plan on having a database made for each campaign, that connects to the master database with all the static tables?

    (I have a hosted limit of 7 databases, so I can't design a DB for every campaign ran)

    EDIT: clarified "...plan on having a database made for each campaign..." as before it sounded like just two databases for the final option.
    Last edited by Mirviriam; 08-27-2010 at 04:06 AM.
    Legacy of Kings: Member

  4. #74
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,373
    Downloads
    48
    Uploads
    1
    Quote Originally Posted by Mirviriam View Post
    Just so we're clear...you're basically saying the server is a rock & 4 times more powerful than I claimed. Which means you feel we should keep the standard tables? Or does it mean you think we should generate a set of database tables for every game? Or do you plan on having a database made for each campaign, that connects to the master database with all the static tables?
    meaning keeping the queries optimized a normal mysql server can handle A LOT
    Quote Originally Posted by Mirviriam View Post
    (I have a hosted limit of 7 databases, so I can't design a DB for every campaign ran)

    EDIT: clarified "...plan on having a database made for each campaign..." as before it sounded like just two databases for the final option.
    there is always the option for using table prefixes.

    for example, birthright.net runs on 1 single DB.. but has the tables for the vbulletin and for the wiki (and some other tables i created in the past) ... and you can even make different DB-users and limit what they can see, update, create etc

    for example, user1 sees all table with prefix camp1_
    user2 sees all tables with prefix camp2_ etc etc

    if you want to create your DB even more generic so it can be used in a multi campaign setting i think it requires an even more drastic redesign.

    i think copying the tables and using a prefix would be an easier solution (and less complicated pivot table and queries)
    Te audire non possum. Musa sapientum fixa est in aure.

  5. #75
    Senior Member Dyark's Avatar
    Join Date
    Jun 2007
    Location
    montreal, canada
    Posts
    163
    Downloads
    99
    Uploads
    2
    That's what I do, create 1 database with fixed tables for the beginning data and different pivot table for each campaign.

  6. #76
    Senior Member Mirviriam's Avatar
    Join Date
    Apr 2008
    Location
    Where the moon cuts the wind.
    Posts
    259
    Downloads
    4
    Uploads
    0
    Quote Originally Posted by Arjan View Post
    meaning keeping the queries optimized a normal mysql server can handle A LOT


    there is always the option for using table prefixes.

    for example, birthright.net runs on 1 single DB.. but has the tables for the vbulletin and for the wiki (and some other tables i created in the past) ... and you can even make different DB-users and limit what they can see, update, create etc

    for example, user1 sees all table with prefix camp1_
    user2 sees all tables with prefix camp2_ etc etc

    if you want to create your DB even more generic so it can be used in a multi campaign setting i think it requires an even more drastic redesign.

    i think copying the tables and using a prefix would be an easier solution (and less complicated pivot table and queries)
    When you had brought up views, that's when I started thinking putting the x_domain_campaign
    x_province_campaign
    idea in to serious consideration.

    Mainly because with view's the sql queries aren't anymore complicated, the data sets aren't more complicated, it's just adding an index table essentially from what I can tell. The view option will hide the campaigns from each other.

    I've never done more than one user table or setup multicampaign before...what makes this prefixing so much better?

    EDIT: I went through & removed campaign_id from all tables in model data base ... also spent an hour fixing name & creating all the proper x_ & md_ tables. Sadly, that's more visible results than my sql spider & my province editor. I keep coming back to the fact I need more information about our layout before I can progress
    Last edited by Mirviriam; 08-30-2010 at 12:54 AM.
    Legacy of Kings: Member

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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
  •  
BIRTHRIGHT, DUNGEONS & DRAGONS, D&D, the BIRTHRIGHT logo, and the D&D logo are trademarks owned by Wizards of the Coast, Inc., a subsidiary of Hasbro, Inc., and are used by permission. ©2002-2010 Wizards of the Coast, Inc.