Create multiple sqlite tables, many-to-many design
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
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.