Media engine database schema

Today (12/07/05), me and Sina began to look potential schemas for the media engine, and ran into some problems.

Our big issue is that of representations of the same thing. If we have 10 images of a painting from various angles, chances are they'll have the same title, so that's not too bad, although ideally they would be linked together. If we have a movie, and several keyframes from the movie, how will the two be related within the database?

One option is to implement a tree structure within the media. A media item can have a parent, so the keyframes will have the movie as their parent. However, this could raise issues about the keyframes being "less important" than the movie, which may not be the case.

We could approach it with a Sculpteur-style Object, Representation approach. Under this schema, an object would be defined that would then have one or more representations. For example, a vase might have pictures of it's front and back. This raises some big problems, however: what happens if there's a movie of the fall of the Berlin Wall? Is the object the Berlin Wall? Therefore, if we add a picture of the Berlin Wall falling, does it have to be related to the Berlin Wall object? What if a movie contains many objects?

I am fairly sure that we should stay media-centric - that is, we should avoid "objects" because they become ambiguous, and stick to storing only representations, but I am uncertain as to how we can achieve this.

13/07/05: Further discussion

After some more thinking, we have decided that the media engine should hold nothing but media. This means that all links between media are considered metadata, and will be dealt with by the metadata engine.

External Links

OntoMedia is a vocabulary for annotating media. Worth a look, I think, as it provides ways of linking media together.

01/08/05 Representation Schema

SET FOREIGN_KEY_CHECKS=0;

create database if not exists `representation_schema`;

use `representation_schema`;

/*
Table structure for rep_format
*/

drop table if exists `rep_format`;
CREATE TABLE `rep_format` (
  `rep_format_id` tinyint(3) unsigned NOT NULL default '0',
  `name` varchar(30) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`rep_format_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*
Table structure for rep_type
*/

drop table if exists `rep_type`;
CREATE TABLE `rep_type` (
  `rep_type_id` tinyint(3) unsigned NOT NULL default '0',
  `name` varchar(30) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`rep_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*
Table structure for representation
*/

drop table if exists `representation`;
CREATE TABLE `representation` (
  `is_primary` tinyint(1) NOT NULL default '0',
  `representation_id` int(11) NOT NULL default '0',
  `uri` varchar(255) NOT NULL default '',
  `type` int(11) NOT NULL default '0',
  `format` int(11) NOT NULL default '0',
  `thumb_uri` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`representation_id`),
  KEY `uri` (`uri`,`thumb_uri`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*
Table structure for sub_representation
*/

drop table if exists `sub_representation`;
CREATE TABLE `sub_representation` (
  `sub_representation_id` int(11) NOT NULL default '0',
  `sequence_number` int(11) default NULL,
  `sub_rep_uri` varchar(255) NOT NULL default '',
  `type` int(11) NOT NULL default '0',
  `format` int(11) NOT NULL default '0',
  `representation_id` int(11) default NULL,
  PRIMARY KEY  (`sub_representation_id`),
  KEY `sub_rep_uri` (`sub_rep_uri`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS=1;
 

Database schema description

representation The root table in the schema is representation. This tables models the representation of an object. This table contains:

   representation_id - the object identifier
   is_primary - A flag to identify the representation as primary for an object
   uri - the URI of the representation
   type - the representation type e.g. 3D model, key frame, movie etc
   format - the media format e.g. tiff, jpg, wmv etc
   thumb_uri - The URI of the representations thumbnail image

sub_representation This table models a sub representation of a representation. For example, a representation could be a video, which has sub representations of the same video but of lower quality and key frames of the video. This table contains:

   sub_representation_id - the sub representation's identifier
   representation_id - the id referencing the representation the sub representation belongs to
   sequence_number - the sequence number of a key frame. Note, this could be null if the sub representation isn't a key frame.
   type - the type of sub representation for example, key frame or video etc
   format - the format of the sub representation for example, jpg or wmv etc

rep_format This simple table represents a format list that a representation can be. The table contains:

   rep_format_id - the id of the format
   name - the name of the format. For example, JPG
   description - description of the format

rep_type This simple table represents a type list that a representation can be. The table contains:

   rep_type_id - the id of the type
   name - the name of the type. For example, key frame
   description - description of the type

Sample Representation XML

 <representation>
	<is_primary>false</is_primary>
	<reference>sch200304221331-004</reference>
	<uri>http://piltdownman.it-innovation.soton.ac.uk:8080/vam/SCH/20030422/movies/004-1.wmv</uri>
	<type>1</type>
	<format>3</format>
	<sub_reps>
		<sub_rep>
			<reference>001</reference>
			<sequence_number>1</sequence_number>
			<uri>http://piltdownman.it-innovation.soton.ac.uk:8080/vam/SCH/20030422/keyframes/001.jpg</uri>
			<type>3</type>
			<format>1</format>
		</sub_rep>
		<sub_rep>
			<reference>002</reference>
			<sequence_number>2</sequence_number>
			<uri>http://piltdownman.it-innovation.soton.ac.uk:8080/vam/SCH/20030422/keyframes/002.jpg</uri>
			<type>3</type>
			<format>1</format>
		</sub_rep>
		<sub_rep>
			<reference>003</reference>
			<sequence_number>3</sequence_number>
			<uri>http://piltdownman.it-innovation.soton.ac.uk:8080/vam/SCH/20030422/keyframes/003.jpg</uri>
			<type>3</type>
			<format>1</format>
		</sub_rep>
		<sub_rep>
			<reference>003</reference>
			<sequence_number></sequence_number>
			<uri>http://piltdownman.it-innovation.soton.ac.uk:8080/vam/SCH/20030422/movies/004-2.wmv</uri>
			<type>1</type>
			<format>3</format>
		</sub_rep>		
	</sub_reps>
	<thumbnail>
		<uri>http://piltdownman.it-innovation.soton.ac.uk:8080/vam/SCH/20030422/thumbs/004.jpg</uri>		
	</thumbnail>
 </representation>