[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Create multiple sqlite tables, many-to-many design

Oops!  Just posted this to the wrong newsgroup.  Sorry!!!

I'm doing a Python app that uses SQLite, and am stumbling on a few 
questions.  I've read a lot of books and documentation, but two 
questions still allude me.  Hope someone that been there done this can 
help.  Below is a note I sent to the SQLite mailing list yesterday.  So 
far, nothing.  Need to get going, so many thanks!

I'm doing an app. that uses sqlite, and has a many-to-many relationship. 
  The areas I need some guidance are:
* Best way to create multiple tables the first time the app. is started.
* How to create a MTM relationship and add/modify data.

I can create tables (Python) by putting the code in discrete functions 
for each table and passing just the path to the database.  I can also 
create a calling function that gets a connection and passes that.  What 
is the pro-con of each since each seems to work?  Better ideas?

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.