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
