[Tutor] Proper SQLite cursor handling?

[boB Stepp]

On Mon, Jul 5, 2021 at 9:22 PM Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
>
> On Mon, 5 Jul 2021 19:20:49 -0500, boB Stepp <robertvstepp at gmail.com>
> declaimed the following:
>
>
> game_id | game_name | strategy |  max_num_reserve_cards |
> max_num_foundation_cards | reserve_card_value | foundation_card_value
> | total_score
> ==================================================================================================================
> 1             | boBSolitair1 | myidea1 | 13
>           | 52                                           | -1
>                    | 1                                   | - 548
> 2             | boBSolitair2 | myidea2 | 13
>           | 52                                           | -1
>                    | 1                                   | 0
> 3             | AlanSolitair1 | hisidea1 | 28
>             | 104                                         | -2
>                     | 1                                   | 291
>
>         Note: if "game name" and "strategy" are always linked one-for-one, you
> should get rid of one of the columns -- or create a table of

What I wrote in an effort to create a non-mangled table (not much luck
there) was to have short names.  In actual usage "game_name" is meant
to be a user-created identifier that ultimately will show up in the UI
menu to select that game.  The "strategy" field will be for an
expanded description of what strategy the user wishes to employ for
that particular game.  It might just be a word or two or be a lengthy
paragraph.

> strategy(*ID*, _game_name_, _strategy_)
>
> and only have one column which is a foreign key (that is, it stores the ID
> from another table, for use in joining tables). Call it "strategy" and drop
> game_name from your main table.
>
>         That allows you to have just "boBSolitair" for "game_name", and records
> with different "strategies"
>
> strategy
> ID      game_name               strategy
> 1       boBSolitair             myidea1
> 2       boBSolitair             myidea2
> 3       AlanSolitair            hisidea1
> 4       boBSolitar              myidea3
>
>         That then makes your first table
> variants
> ID      strategyID      ...
> 1       1                       ...
> 2       2                       ...
> 3       3                       ... (and yes, the example data is rather redundant looking
> 4       4                       ...
>
> >Each one of these solitaire games would have its own collection of
> >hands played over time and look like:
> >
> >boBSolitair
> >=========
> >hand_num | date_played | time_recorded | hand_score
> >==========================================
> >1                | 2021-07-04  | 1342                | -5
> >2                | 2021-07-04  | 1356                | 43
> >3                | 2021-07-05  |  0704               | -26
> >4                ...
> >
> >Likewise "boBSolitair2" and "AlanSolitair1" would have their own hands
> >played tables.
> >
>         And that is where the problem comes in.
>
>         You should NOT have a table for each variant. Instead you should have
> one table, which has a field pointing to the variant to which it applies...
>
>         Calling it "hand_number" is meaningless -- it is just a primary key ID
> field

I disagree.  It has the meaning of the 1st hand played, the second
hand, etc. This also makes it a good primary key.

> plays
> ID      variantID       date_played             time_recorded   score
> 1       1                       ...
> 2       2                       ...
> 3       3                       ...
> 4       1                       ...
> 5       1                       ...
> 6       3                       ...
> 7       4                       ...
>
>
>         SELECT s.game_name, s.strategy, v.max_num_reserved_cards, v. ...,
> p.date_played, p.time_recorded, p.score from strategy as s inner join
> variant as v on v.strategyID = s.ID inner join plays as p on p.variantID =
> v.ID where s.game_name = "AlanSolitair" and s.strategy = "hisidea1";
>
> (that should return the plays with ID 3 and 6).
>
>         Have you read any tutorials on relational database design (these should
> be engine agnostic -- and cover things like at least the first three Codd
> Normal Forms).

Yes and no.  Yes, too many years ago and at the time I never did more
than play around a little bit with databases.  No, in that I have
obviously forgotten what I had all too briefly known.  Nonetheless I
suspected I was making things much harder than they should be, which
is why I ask these questions...

> https://www.codementor.io/@nigelbpeck/design-principles-for-relational-data-tzzujzkiq
>
> https://medium.com/@expertwithsagarjaybhay/principles-relational-database-5fad42e888af
> (one disagreement: In relational theory, the non-key data is related to the
> primary key WITHIN a single table; that first bullet is talking about
> joining data from multiple tables...
>         Theory                          Practice
>         relation                                table
>         tuple                           row
> )
> https://medium.com/@kimtnguyen/relational-database-schema-design-overview-70e447ff66f9
> (also uses the non-theory definition of "relation" -- but does mention 1NF,
> 2NF, 3NF)
>
>         Wikipedia appears to be the best reference (and uses relation correctly
> as representing A-TABLE).
> https://en.wikipedia.org/wiki/Relational_model
> https://en.wikipedia.org/wiki/Database_normalization
>
>         For most/casual uses, 3NF is sufficient.

Thanks for these references.  I also have plenty of books on these
topics however dusty they be....

boB Stepp