Thanks Derek, Cliff and Alec for all your help.
Web2py has become a great community.
On Mon, Oct 1, 2012 at 8:25 PM, Alec Taylor <alec.tayl...@gmail.com> wrote:
> Use MySQL Workbench, throw a screenshot of your schema as an ERD on
> the #mysql channel on freenode.
>
> They'll give you what you're looking for, i.e.: a rational schema.
>
> On Tue, Oct 2, 2012 at 10:08 AM, Cliff Kachinske <cjk...@gmail.com> wrote:
>> Read up on indexing tables. Properly indexed tables can retrieve data much
>> more quickly because they cut down the number of rows the dbms has to
>> examine.
>>
>> This is especially true across joins. MySQL does not index foreign keys so
>> joined queries can take a looooooooong time.
>>
>>
>> On Monday, October 1, 2012 9:27:42 AM UTC-4, curiouslearn wrote:
>>>
>>> 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.t...@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 <curiou...@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.t...@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 <curiou...@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()
>>> >>>>>>
>>> >>>>>>
>>> >>>>>>
>>> >>>>> --
>>> >>>>>
>>> >>>>>
>>> >>>>>
>>> >>>>
>>> >>>> --
>>> >>>>
>>> >>>>
>>> >>>>
>>> >>>
>>> >>> --
>>> >>>
>>> >>>
>>> >>>
>>> >>
>>> >> --
>>> >>
>>> >>
>>> >>
>>> >
>>> > --
>>> >
>>> >
>>> >
>>
>> --
>>
>>
>>
>
> --
>
>
>
--
