hpr2260 :: Managing tags on HPR episodes - 2
Looking for the best way to store and manage tags in the HPR database, part 2
Hosted by Dave Morriss on 2017-03-31 is flagged as Explicit and is released under a CC-BY-SA license.
HPR, database, schema, tag, SQL, foreign key.
(Be the first).
The show is available on the Internet Archive at: https://archive.org/details/hpr2260
Listen in ogg,
spx,
or mp3 format. Play now:
Duration: 00:24:37
general.
Managing tags on HPR episodes - 2
Introduction
This is the second show looking at the subject of Managing Tags.
In the first show we looked at why we need tags, examined the present system and considered its advantages and disadvantages.
In this episode we will look at a solution using a separate table of tags.
Long notes
This is a detailed subject so I have written out a set of longer notes for this episode and these are available here.
Links
- Previous show in this set:
- “Managing tags on HPR episodes - 1” - episode 2245
- HPR web page listing shows missing summaries and tags
- Stack Overflow article about splitting CSV data
- A more detailed article about tags in a database
- HPR show 1965 “Adding SQLite as a datasource to SQLeo”
- Resources:
- Detailed notes for this episode
- SQL to define a ‘
tags’ table, its indexes and code to populate it:normalise_tags_1.sql - SQL to perform the emptying of the ‘
tags’ table and its repopulation:refresh_tags.sql - SQL to find all shows related to a given show using tags:
find_shows_sharing_tags.sql - Perl script to manage the tag table:
refresh_tags