On 8/13/19 4:45 PM, MRAB wrote:
On 2019-08-13 19:59, Chris Angelico wrote:
On Wed, Aug 14, 2019 at 4:50 AM Dave via Python-list
<python-list@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.

Might I also suggest dropping unnecessary prefixes from the field names. For example, "hike_comments" in "Hiking_Table" can be called just "comments" because it's clear from the context that a field called "comments" in the hiking table will contain comments about hiking, if you see what I mean.

I do indeed. I did that so it was easy for everyone to follow. Having started with assm. and C, I have to remind myself to be more explanatory in naming. Guess I over-did it. The actual code is different. htbl, ttbl, jtbl, etc. Too short?

Dave,
--
https://mail.python.org/mailman/listinfo/python-list

Reply via email to