hpr_generator

Archived

Table of Contents

Some thoughts on database enhancements

Some thoughts on database enhancements

Some work was done on an alternative to the MySQL/MariaDB database a few years ago, using PostgreSQL. We decided against this database engine but the design might have some features that are helpful.

These are ideas from the Pg database(s) in no particular order:

Merging of the Today with a Techie shows with the HPR shows.
- In my design I had a simple integer primary key and an episode key like twat001 and hpr001 to differentiate the two origins.
Licenses in the episodes table as foreign keys into a licenses table
IA status information in the episodes table such as an archived column to show the IA status along with IA dates, and URLs.
Show status like reserved, processing and posted (an enumerated type) as a column in the episodes table
An episode->host relationship to allow multiple hosts per show (as well as the current multiple shows per host). Implemented through an episodes_hosts_xref cross-reference table.
An episode->series relationship as at present. Implemented through an episodes_series_xref cross-reference table.
An episode->tag relationship. Implemented through an episodes_tags_xref cross-reference table.
Comments exactly as now. A trigger maintains a last_changed column (as now).
An assets table with a FK relationship with the episodes table.

Further suggestions:

A means of detecting changes in the notes (or other attributes) for each show. Perhaps a note hash column? Perhaps a last_changed column linked to a trigger? The point is, if we're maintaining parity between the HPR copies and the IA copies of these shows we need a way of detecting changes.