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

Create multiple sqlite tables, many-to-many design

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 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.
> 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?