View this PageEdit this PageUploads to this PageHistory of this PageHomeRecent ChangesSearchHelp Guide

Database Design

The information that makes up the content of the library is spit up into a set of tables based on content and library functionality. The media content is placed in to a light-weight table called Media and then divided into sub-tables based on the content: Music, Sounds, Images, Text, and Movies. Only the Music table was implemented for this preliminary version of the library. Other tables were built for other kinds of information such as: People, Organizations, and Collections.

Two more maintenance tables were constructed for bookkeeping functionality in the library. A table called Data was created to hold “global variables” in the library such as id number generators and host addresses. Another table called Changes was made to log modifications made to content to allow lost information to be recovered and changes be rolled back. All the tables and their fields are listed below:
Main Table
Music Table
Person Table
Organization Table
Media Creator Table
Collection Table
Data Table
Changes Table
Users Table

Main Table:

CodeNameContributorCategoryMedia CreatorFile LocationFile TypeRightsDC File
Every media entry in the Media Library will have a corresponding entry in the main table. This table keeps track of the bookkeeping data necessary for organizing the content. The actual metadata about the media will be housed in Tables for specific types of media such as music or images. Entries about non-media entities (persons, organizations) will not exist in the main table, they will only exist in their categorical table.
Code: Unique ID code for artifact in library, generated sequentially. No two entries in the library will have the same id number
Name: The full name given the artifact.
Contributor: Library user who originally created the artifact.
Category: The Library category that the artifact falls under. The categories are currently:
  • Music
  • Sounds
  • Images
  • Text
  • Video
  • ...New categories can be defined after the library has been started.
Media Creator: The ID of the creator of the content, as a special entry in the library. (Usually some kind of publishing company)
File Location: The link to the actual media file, including protocol.
File Type: The MIME type of the file.
Rights: A description of the rights attached to the resource.
DC File: A Dublin Core Metadata file in XML for the media.

Music Table:

CodeLengthKeyComposerPerformerRecording DateRecord LabelScoreDescription TextXMD
The Music Table is designed to be a container for generic musical recordings. Media that falls under this umbrella is:
  • Recordings of Classical Music
  • Recordings of Current Music
  • Recordings of Live performances
  • Electronic Music encoded in either sound or MIDI-type format
The table can be broken up in the future into sub classes of music if it becomes cumbersome.
Code: The unique ID code for the artifact in the library
Length: The duration in seconds of the media artifact
Key: The key the work is written in (primarily)
Composer: The code of the person who wrote the music. The person should exist in the persons table.
Performer: The organization or person that performed the music for this recorded version.
Recording Date: The date that the recording was made, in ISO 8601 format, YYYY-MM-DD.
Record Label: The music publishing organization responsible for the production of the contents of the media. Represented as a code that links to an entry in the Media Creator table.
Score: A representation of the score with a MIME type for it. Can be a reference to a file housed outside of the database. (Note: need to check MIME types for score markup, such as lilypad, and Music XML…) Preferred format is a MIME type for an image, and a link to where the image is hosted.
Description Text: An HTML write up on the music. Similar to the articles on WikiPedia. Will be encapsulated in an XML File that holds versioning information, and a link to a file containing the past versions of the description. See Description Text section for more information on the how the Description Text is handled.
XMD: The extensible Metadata section. See Extensible Metadata section for more information on how the extensible metadata is handled.

Person Table:

CodeNameClassificationAffiliationsBirth DateDeath DateWorksBiographyXMD
The person table is for storing individuals such as musicians, composers, painters, sculptors, artists, and other “creators of media.” There will only be one entry in the table per individual, and the maintainers of the database will make sure that this is an invariant. The persons are referenced from the other tables, but a user in the library will be able to search and look for individuals.
Code: The unique ID code for the individual. Will be constructed from the first 8 letters of the last name, then first name, then middle name, then filled with ‘X’ if there are not enough letters. A number ordered in the creation of the code to resolve ID collisions will follow the letters.
Name: This field will contain the full name of the individual, including titles.
Classification: A brief descriptive string about the profession and type of person. (include gender and ethnicity?)
Affiliations: References to any organizations in the library that the person was affiliated with. The reference will be the unique ID code for the organization.
Birth Date: The date that the person was born, in standard ISO 8601 format.
Death Date: The date that the person dies, in standard ISO 8601 format. If the individual is still alive, then this field will be empty.
Works: A list of works that the person has done or contributed to. If the work is also in the library, then the name of the work will also be accompanied by the ID codes of entries in the library that are all or part of that work.
Biography: This is biographical information on the person represented by the entry. Is versioned, and follows the same rules as the Description Text in media entries. See the section on Description Text for more information
XMD: The extensible metadata section. See Extensible Metadata for more information on how the extensible metadata is handled.

Organization Table:

CodeNameClassificationMembersStructureDescription TextXMD
The organization table is for storing information about non-person entities, such as corporations, bands, orchestras, labs, or any other type of organization.
Code: The unique identifier for the organization. It is generated the same way as the codes for other entities in the library.
Name: This field will contain the full name of the organization.
Classification: A brief descriptive string about what type of organization the entry is: band, corporation, etc.
Members: Key persons affiliated with the organization
Structure: The layout of the organization with regards to the members (? Think about this field more).
Description Text: An HTML writeup on the organization. Handled the same as the Description Text fields in other tables. See section Description Text for more details.
XMD: Any extensible metadata added to the entry see section on Extensible Metadata for more information on the field.

Media Creator Table:

CodeNameOrganizationCopyright Permissions Data
Not User-modifiable
The media creator table is used to house copyright information about the various providers of content to the library. The copyright permissions data format will be clarified after consultation with legal experts.
Code: The unique identifier for the Media Creator. It is formatted in the same way as all other ID codes in the library.
Name: The name of the Media Creator
Organization: The code of the organization in the Organization table that corresponds to the Media Creator
Copyright Permissions Data: The copyright information of the content provider.

Collection Table:

CodeNameTypeItemsXMD
The collection table is used to store information about groupings of media. A collection is a set of media that has some kind of relation, enumerated in “Type.” Examples of collections are: Albums, Symphonies, Suites, etc.
Code: The unique identifier for the collection. It is formatted the same way as all the other codes in the library.
Name: The name of the Collection.
Type: The type of the collection.
Items: A comma-delimited list of the codes of the items in the collection.
XMD: Any extensible metadata added to the entry see section on Extensible Metadata for more information on the field.

Data Table:

DataValue
The data table is used to hold “global variables” for the library. Some of the data includes the id number generator, information for general information such as the “featured media” that is displayed on the front page, and paths to host servers.
Data: The name of the information stored in the row of the table.
Value: The contents of the data.

Changes Table:

IDTableFieldPrevious_dataUserDate
The changes table holds a log of all the changes that are made to the library. It is used to provide the ability to roll back changes or see a history of a particular entry.
ID: The id number of the media/person/organization/collection that was changed.
Table: The name of the table that the change was made in.
Field: The field that was changed
Previous_data: The data that was in the field before the change was made.
User: The username of the person that made the change (This will be used when user authentication is implemented in the library).
Date: The date and time stamp of the change.

Users Table

user_namepasswordnamegroupemail
The Users table holds entries for all the registered users of the library. It is used to provide people with a personalized home page, track changes, and keep track of the behavior of specific users of the library.
user_name: The login name of the particular user. They are unique identifiers.
password: The password for the user
name: The real name of the user
group: The group that the user belongs to. The groups are used to provide permissions and abilities in the library.
email: The user's email address.

Link to this Page

  • Documentation last edited on 15 February 2005 at 2:40 pm by lawn-199-77-213-141.lawn.gatech.edu