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.