Create multiple sqlite tables, many-to-many design

[Dave]

On 8/13/19 2:59 PM, Chris Angelico wrote:
> On Wed, Aug 14, 2019 at 4:50 AM Dave via Python-list
> <python-list at python.org> wrote:
>> Some of the tables are related.  For example:
>>
>> Hiking_Table             Trails_Table            Joining_Table
>> -----------------        --------------------    -----------------
>> hike_id     PK           trail_id  PK            hike_id   FK
>> hike_date  TEXT          trail_name  TEXT        trail_id   FK
>> hike_destination TEXT    trail_rating REAL
>> hike_rating  REAL        trail_comments TEXT
>> hike_comments  TEXT
>>
>> So far, so good.  I know how to create the tables.  What I am struggling
>> with is how do I insert data into the joining table or don"t I?  If so,
>> do I need to query the other two tables to get the auto-number ID's?
>> Some things I have read suggest that the joining table just contains
>> references, so there is no actual insert.  A pointer to information how
>> to do this would be appreciated.  As for queries, I think I use joins,
>> but a pointer on how to do this would also be appreciated.
> 
> The joining table is a real thing, and will have real inserts. It
> might be easier to think of this as two separate one-to-many
> relationships; for the sake of demonstration, I'm going to add another
> column to your joining table.
> 
> hike_sections ==> hike_id references hikes, trail_id references
> trails, companion_name
> 
> You've decided to have someone with you for some sections of your
> hike. As such, what we have is a number of "mini-hikes" that make up a
> single hike (that's a one-to-many relationship between hikes and
> sections), and also a single trail can be a section of any number of
> hikes (so, another one-to-many relationship between trails and
> sections). For any given section, there is exactly one companion.
> 
> Does this make the many-to-many relationship a bit easier to
> understand? It'll work exactly the same way even if you have no
> ancillary information in that joining table.
> 
> ChrisA
> 
Chris,

Thanks for the note.  I get the theory of MTM and the join table.  It is 
the implementation I don't get.  Let me see if I can demonstrate my 
confusion using pseudo code.

def dbTables_create (dbpath):

     sql_HikeTable = """ CREATE TABLE IF NOT EXISTS hike (
                             hike_id INTEGER AUTO_INCREMENT PRIMARY KEY,
                             hike_date TEXT,
                             hike_destination TEXT,
                             hike_rating REAL,
                             hike_comments TEXT ) """

     sql_TrailTable = """ CREATE TABLE IF NOT EXISTS trail (
                             trail_id INTEGER AUTO_INCREMENT PRIMARY KEY,
                             trail_name TEXT,
                             trail_rating REAL,
                             trail_comment TEXT ) """

     sql_JoiningTable = """ CREATE TABLE IF NOT EXISTS hike_trail (
                             hike_id INTEGER
                             trail_id INTEGER ) """

     # Some more code to open connection, create cursor, execute SQL.

def getUserInput ():
     # Code to get the user input.
     # The user input is:
     hdate = "2019-05-28"
     hdestination = "Top of White Face Mountain, NY."
     hrating = 5.0	# Rating scale 1.0 (bad) to 5.0 (perfect).
     hcomments "Got to do again.  Better shoes needed."
     tname1 = "Brookside"
     trating1 = 4.5
     tcomments1 = "Easy"
     tname2 = "Wilmington Trail"
     trating2 = 4.9
     tcomments2 = "Awesome!!"

def dbDataInsert():

     sql_HikeInsert = """ INSERT INTO hike (
                              hike_date,
                              hike_destination,
                              hike_rating,
                              hike_comments )
                          VALUES (
                              hdate,
                              hdestination,
                              hrating,
                              hcomments ) """

     sql_TrailInsert = """ NSERT INTO trail (
                              trail_name,
                              trail_rating,
                              trail_comment )
                           VALUES (
                              tname1,
                              trating1,
                              tcomments1 ) """

     sql_TrailInsert = """ NSERT INTO trail (
                              trail_name,
                              trail_rating,
                              trail_comment )
                           VALUES (
                              tname2,
                              trating2,
                              tcomments2 ) """

     """ ---> Now what?  I need to populate the join (hike_trail) table.
              Do I query the tables to get the id's?  Is there another
              way?  This is the part I really don't get.  """

Dave,