Schema Part Deux

I forgot to mention, the schema isn't entirely set in stone, and I'm currently awaiting a reply from the person who currently manages our archive database to try to find out if I'm missing anything, so that I can fill it in sooner rather than later.

Database Schema

I've installed the groundwork for the Broadcast Server's MySQL database schema. See below for what it is currently. Now I can get started on setting up a very basic user interface that incorporates the features present in this database (or alternatively, work on the backend which will use much of the same tables). Right now the database will provide a very simple setup compared to what will eventually hopefully be done - it is structured to allow adding videos to the library along with a simple description (as well as optionally marking them as members of a series, which can itself have it's own description) and scheduling individual archived MPEG videos for one-time showings.

Eventually, things I want to be able to do include allowing inserting playlists and SeriesEpisodes into the program schedule, instead of simple MPEGs. SeriesEpisodes will be a special construct that can be inserted into the schedule that acts like a regular scheduled item, but dynamically picks an episode in a selected series when the SeriesEpisode construct instance comes up in the schedule to be played. Each SeriesEpisode will be able to be configured to pick an episode of a series - I imagine chronologically, which will mean some sort of program counter will be required - which will refer to an MPEG file at playtime. Also, to make SeriesEpisodes much more useful, I'd love to set up some sort of system for recurring schedule items, as our week-to-week schedule probably won't vary too too much. Finally we may eventually add support for scheduling in items to be played off of external decks, which may or may not be necessary given TUTV's planned near-total conversion to MPEG digital video.


For anyone interested, the SQL Create Table Syntax used is:

-- Database: `TUTV`
--

-- --------------------------------------------------------

--
-- Table structure for table `BroadcastSchedule`
--

CREATE TABLE `BroadcastSchedule` (
`BSStartTime` datetime NOT NULL,
`BSElemType` enum('Program') collate utf8_unicode_ci NOT NULL default 'Program',
`BSElemID` int(11) unsigned NOT NULL,
PRIMARY KEY (`BSStartTime`),
KEY `BSElems` (`BSElemType`,`BSElemID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `Config`
--

CREATE TABLE `Config` (
`config_name` varchar(255) collate utf8_unicode_ci NOT NULL,
`config_value` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`config_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `HDStorage`
--

CREATE TABLE `HDStorage` (
`PID` int(11) unsigned NOT NULL,
`HDSFileName` varchar(255) collate utf8_unicode_ci NOT NULL,
`HDSAccessible` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`PID`),
UNIQUE KEY `HDSFileName` (`HDSFileName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `Programs`
--

CREATE TABLE `Programs` (
`PID` int(11) unsigned NOT NULL auto_increment,
`PName` varchar(500) collate utf8_unicode_ci NOT NULL,
`PDesc` mediumtext collate utf8_unicode_ci NOT NULL,
`SID` int(11) unsigned default NULL,
`PRecordedDate` date default NULL,
`PLength` time default '00:00:00',
`PLengthFrame` tinyint(3) unsigned NOT NULL default '0',
`PStorageMethod` enum('N/A','HD','ExtMedia') collate utf8_unicode_ci NOT NULL default 'N/A',
`PEntryTime` datetime NOT NULL,
`PUpdatedTime` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`PID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `Series`
--

CREATE TABLE `Series` (
`SID` int(11) unsigned NOT NULL auto_increment,
`Name` varchar(255) collate utf8_unicode_ci NOT NULL,
`Desc` mediumtext collate utf8_unicode_ci NOT NULL,
`NumShows` mediumint(8) unsigned NOT NULL,
`TypicalShowLength` time NOT NULL,
PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `Users`
--

CREATE TABLE `Users` (
`UserName` varchar(20) collate utf8_unicode_ci NOT NULL,
`UserPassword` varchar(20) collate utf8_unicode_ci NOT NULL,
`UserEnabled` tinyint(1) NOT NULL default '1',
`UserAccess` set('Administrator') collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`UserName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;