Re: [sqlite] Create multipe tables, many-to-many design

2019-08-14 Thread Keith Medcalf
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 
>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 TEXTtrail_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


Re: [sqlite] Create multipe tables, many-to-many design

2019-08-14 Thread Keith Medcalf

>  UNIQUE (date, destination)

should of course be UNIQUE (start, destination)

>create index HikedTrails on HikeTrail (hike_id, trail_id);
>create index TrailsHiked on HikeTrail (trail_id, hike_id);

Both of these should be UNIQUE indexes and could be declared in the create 
table ...

and of course foreign Key enforcement should be enabled

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


Re: [sqlite] Create multipe tables, many-to-many design

2019-08-14 Thread Keith Medcalf

On Monday, 12 August, 2019 14:05, dboland9  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 TEXTtrail_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