For maximum space efficiency and using a supporting version of SQLite3 you 
could declare the N:M table as:

create table HikeTrail
(
  hike_id INTEGER NOT NULL REFERENCES Hikes,
  trail_id INTEGER NOT NULL REFERENCES Trails,
  PRIMARY KEY (hike_id, trail_id),
  UNIQUE (trail_id, hike_id)
) WITHOUT ROWID;

because really you only need the N:M and M:N index trees.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Wednesday, 14 August, 2019 11:33
>To: SQLite mailing list
>Subject: Re: [sqlite] Create multipe tables, many-to-many design
>
>
>On Monday, 12 August, 2019 14:05, dboland9 <dbola...@protonmail.com>
>wrote:
>
>>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.  Somethings 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 problem is that you have not identified the keys properly.  What
>you call the PK is certainly a candidate, but it is not the actual
>key that identifies the unique data, but rather is rather a pseudokey
>identifying a row, not a set of unique data identifying a row.  Once
>you identify the actual keys, you are in business.  I would guess
>that the actual tables probably might look as follows (I have removed
>the extraneous embedding since it adds nothing except more typing,
>and changed your "date" into a starting datetime):
>
>create table Hikes
>(
>  id INTEGER PRIMARY KEY,
>  start TEXT NOT NULL CHECK (start IS datetime(start, '+0 days'),
>  destination TEXT NOT NULL COLLATE NOCASE,
>  rating REAL,
>  comments TEXT,
>  UNIQUE (date, destination)
>);
>create table Trails
>(
>  id INTEGER PRIMARY KEY,
>  name TEXT NOT NULL COLLATE NOCASE UNIQUE,
>  rating REAL,
>  comments TEXT
>);
>create table HikeTrail
>(
>  hike_id INTEGER NOT NULL REFERENCES Hikes,
>  trail_id INTEGER NOT NULL REFERENCES Trails
>);
>create index HikedTrails on HikeTrail (hike_id, trail_id);
>create index TrailsHiked on HikeTrail (trail_id, hike_id);
>
>Assuming that you want to ADD a new hike on (start, destination,
>name), then you do the following sql:
>
>BEGIN IMMEDIATE;
>INSERT OR IGNORE INTO Hikes (start, destination)
>                 values (:start, :destination);
>INSERT OR IGNORE INTO Trails (name)
>                 values (:name);
>INSERT INTO HikeTrail (hike_id, trail_id)
>       VALUES ((select id from Hikes where start=:start and
>destination=:destination),(select id from Trails where name=:name));
>COMMIT;
>
>To DELETE a hike on (start, destination, name) you do the following:
>
>BEGIN IMMEDIATE;
>DELETE FROM HikeTrail
>      WHERE hike_id = (select id
>                         from Hikes
>                        where start=:start
>                          and destination=:destination)
>        AND trail_id = (select id
>                          from trails
>                         where name=:name);
>DELETE FROM Hikes
>      WHERE start=:start
>        AND destination=:destination
>        AND NOT EXISTS (select hike_id from HikeTrail where hike_id =
>id);
>DELETE FROM Trails
>      WHERE name=:name
>        AND NOT EXISTS (select trail_id from HikeTrail where trail_id
>= id);
>COMMIT;
>
>>Perhaps these topics could be added to the excellent
>http://www.sqlitetutorial.net/ .
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to