Thanks Alec. I don't think I will reach those numbers, ever, for one game.
This has nothing to do with web2py, but I have a MySQL database with
about a 700,000 records and selects are quite slow in it. Some simple
queries take 7-8 seconds per query, which is slow in the context of a
game. Probably I have not set it up well. But that is what caused me
to worry about this.
Anyhow, thanks for the advice.
On Mon, Oct 1, 2012 at 9:13 AM, Alec Taylor <alec.tayl...@gmail.com> wrote:
> Those are really low numbers.
>
> When you are getting closer to a billion games a week you should
> consider a schema change, e.g.: multi-tenant.
>
> On Mon, Oct 1, 2012 at 10:54 PM, Curiouslearn <curiousle...@gmail.com> wrote:
>> Hi Alec,
>>
>> I am using mysql backend. I will most probably host it on dotcloud,
>> using their Python and MySQL service.
>>
>> I think as suggested by Cliff and you, I will create only one set of
>> tables for all games. As of now I will probably have only about 50
>> datapoints per team. There are no images. Right now I don't anticipate
>> more that 20 games per 2-3 months; hence about 20000 (20 teams per
>> game x 50 datapoints X 20 games) data points per 2-3 months. I was
>> thinking of what would happen when more people use it. But I suppose
>> I should not worry about that now.
>>
>> Thanks.
>>
>>
>> On Mon, Oct 1, 2012 at 7:48 AM, Alec Taylor <alec.tayl...@gmail.com> wrote:
>>> Probably not. Where are you hosting this?
>>>
>>> E.g.: Google App Engine doesn't have "tables", so the whole concept is
>>> "irrelevant" there.
>>>
>>> Also, how much data are you talking per team, and how many teams do
>>> you expect to be in the database?
>>>
>>> Unless you're storing an inordinate amount of images or realtime VOIP
>>> recording you won't need to worry about efficiency.
>>>
>>> On Mon, Oct 1, 2012 at 9:21 PM, Curiouslearn <curiousle...@gmail.com> wrote:
>>>> Hi Cliff,
>>>>
>>>> Thanks very much for your input. I am new to databases and appreciate
>>>> the advice.
>>>>
>>>> I was also thinking of the alternative you are talking about. But
>>>> thought that as the number of games played increased, this would
>>>> increase the number of records in the tables and make database access
>>>> slow. Is this not a good reason to create different set of tables for
>>>> each game? Would appreciate any advice regarding this.
>>>>
>>>> Thanks.
>>>>
>>>> On Mon, Oct 1, 2012 at 4:46 AM, Cliff Kachinske <cjk...@gmail.com> wrote:
>>>>> Do not let your tables proliferate this way.
>>>>>
>>>>> You need a teams table, even if it contains only the team name or some
>>>>> other
>>>>> identifier.
>>>>>
>>>>> You need a games table.
>>>>>
>>>>> You need to relate these two. One game has multiple teams, I suppose, so
>>>>> that makes a one-to-many relationship.
>>>>>
>>>>> If the same team can participate in more than one game, you need a many to
>>>>> many relationship.
>>>>>
>>>>> The DAL chapter in the Web2py manual explains how to implement these.
>>>>>
>>>>> Next you add a game_id field to both your offers and decisions tables.
>>>>> That
>>>>> ties these events to the game. Alternatively you could simply call the
>>>>> field 'game', assuming you can remember that it contains the record id of
>>>>> the game in question.
>>>>>
>>>>> You also need to add a team_id field to these tables. That ties each
>>>>> record
>>>>> to the team involved.
>>>>>
>>>>> This data structure will allow you to select records for each game, for
>>>>> each
>>>>> team in a game. If the teams persist, you can also select all the records
>>>>> related to the team.
>>>>>
>>>>> On Sunday, September 30, 2012 5:31:50 PM UTC-4, curiouslearn wrote:
>>>>>>
>>>>>> Hello,
>>>>>>
>>>>>> This is a question about recommended practice for doing the following:
>>>>>>
>>>>>> I want to create a web interface for creating a setup for new games. For
>>>>>> example, the web interface will let me specify
>>>>>> name of the game, number of teams etc. Based on this information I want
>>>>>> to
>>>>>> create new database tables for the game.
>>>>>> Should the table definitions be given in a controller function, such as
>>>>>> in
>>>>>> the example below? Is that the recommended way
>>>>>> to do this, or is there another way that you would recommend.
>>>>>>
>>>>>> Thank you.
>>>>>>
>>>>>> **Controller function for creating tables**
>>>>>>
>>>>>> def createtables():
>>>>>> if request.post_vars:
>>>>>> experimentname = request.post_vars.experimentname
>>>>>> numteams = int(float(request.post_vars.numteams))
>>>>>> teams_tablename = "{0}_teams".format(experimentname)
>>>>>> offers_tablename = "{0}_offers".format(experimentname)
>>>>>> ardecisions_tablename = "{0}_ardecisions".format(experimentname)
>>>>>> migrate_teamstablename = "{0}.table".format(teams_tablename)
>>>>>> migrate_offerstablename = "{0}.table".format(offers_tablename)
>>>>>> migrate_ardecisionstablename =
>>>>>> "{0}.table".format(ardecisions_tablename)
>>>>>> db.define_table(teams_tablename,
>>>>>> Field('teamname', 'string', length=40, required=True,
>>>>>> unique=True, notnull=True),
>>>>>> Field('passwd', 'password'),
>>>>>> Field('role', 'string', length=20, required=True,
>>>>>> default='NA'),
>>>>>> format = '%(teamname)s', migrate=migrate_teamstablename)
>>>>>> # Table showing the ask amount of the first mover
>>>>>> referencestring = 'reference {0}'.format(teams_tablename)
>>>>>> db.define_table(offers_tablename,
>>>>>> Field('round', 'integer'),
>>>>>> Field('askamount', 'integer'),
>>>>>> Field('payoff', 'integer'),
>>>>>> Field('teamname_id', referencestring),
>>>>>> migrate = migrate_offerstablename)
>>>>>>
>>>>>>
>>>>>> # Table accept-reject decisions
>>>>>> db.define_table(ardecisions_tablename,
>>>>>> Field('round', 'integer'),
>>>>>> Field('acceptorreject', 'string', length=2),
>>>>>> Field('payoff', 'integer'),
>>>>>> Field('teamname_id', referencestring),
>>>>>> Field('offerer_id', referencestring),
>>>>>> migrate = migrate_ardecisionstablename)
>>>>>>
>>>>>>
>>>>>> teamnames = maketeamnames(numteams)
>>>>>> for tname in teamnames:
>>>>>> db[teams_tablename].update_or_insert(teamname=tname)
>>>>>> db.experimentlist.insert(experimentname=experimentname)
>>>>>> return dict()
>>>>>>
>>>>>>
>>>>>>
>>>>> --
>>>>>
>>>>>
>>>>>
>>>>
>>>> --
>>>>
>>>>
>>>>
>>>
>>> --
>>>
>>>
>>>
>>
>> --
>>
>>
>>
>
> --
>
>
>
--
