Canadian Musician Robert Aitken:Database Design

From Canadian Musician Robert Aitken
Jump to: navigation, search

(from file "RA database design.rtf"; the page is to replace that file, which will no longer be used)

14.02.17 RA Database design

15.07.05 Rethink in terms of Semantic Media Wiki (SMW) or Cargo. Cargo does not use Properties, as does SMW. Compared with SMW, it has more SQL-like capabilities and more sophisticated querying.

Strategy

  • work backwards from purpose (types of data and types of queries and their purpose and value) and from data entry process
  • start with something manageable; e.g., recordings and then test its use; rework as needed

Design Approach

  • draw up real samples of typical pages
  • determine from where they would draw their data
  • refine the data structure
  • design the tables, and create the associated templates and forms
  • test by entering samples of each type of data
  • get some people who haven’t been involved to examine it and do a structured review.

Table Structure

  • Table names will have plural names with no spaces (e.g., Concerts, Pieces), with the exception of FluteTeaching; each will have a corresponding category or set of categories, template, and data-entry form titled with the singular form of the same name (e.g. Concert, Piece)
  • For some tables, the page associated with a row in the table will have its name constructed from the values of several fields. This is to ensure unique and consistently formed page names without the need for people who enter data to know the naming convention. The specifications indicate how the page names are constructed. They are in the (informal) form: page name="text" field1 "text" field2 "text" . . . In some cases, delimiters (period or emdash) will be used between parts of the title.
  • For fields whose values are page names constructed from multiple fields in the table with which those pages are associated, the date entry for those fields will have to be done first in the other table(s). For example, Concerts has a PiecesPerformed field, whose data comes from the PiecePerformances table (which holds titles of pieces and lists of instruments and performers who played them). That table has a Piece field whose data come from the Pieces table. Entries should be made first in the Pieces table of all the pieces appearing in the concert. If an entry has not been made for the composer, one should be made in the Composers table. Data can then be entered in Concerts except for the list of Pieces Performed. Then the Pieces Performed entries should be done, since they need to include the associated Concert. After that, the list of Pieces Performed should be entered in the associated Concerts table. Before all these, data should be entered for the References table, so that it can be included as information source in the other tables. A program document might be the primary source for information about pieces, piece performances, composers, and performers. Some of this information will be in program notes, which in some cases have information about both pieces and composers.
  • fields are listed in the form: field (type)
Most tables 
  • fields: DataSource (Page, autocompletion on Category:Reference), Status (Boolean)
  • for keeping track of source (useful for verification and proper citation) and status (complete/incomplete) of the record; the wiki keeps track of date of entry and contributor so that information does not need to be explicitly put in the table
  • all sources will have a page and will appear in the References table

References

  • fields: Title (Text), Authors (list (;) of Text), Editor (Page), PublicationDate (Date), Source (Text), Publisher (Text) PublisherWebSite (URL), Language (Text), Abstract (Text), Medium (Text), Status (Boolean)
  • page name=Title PublicationDate
  • entries are sources of data
  • for unpublished work, date of publication will be date of creation if known
  • use table as a reference list for the site; use Cite to refer to Reference pages
  • if source is a file such as an audio or video recording or transcript, the name of the file can be given, together with a link if it is available

Category:Reference Template:Reference Form:Reference Special:CargoTables/References

Personal Events

  • fields: EventName (Text), EventType (Text), StartDate (Date), EndDate (Date), DateRange (StartDate - EndDate), City (Text), Country (Text), Location (Coordinates), Description (Text), DataSource (Page), Status (Boolean)
  • page name=EventName DateRange
  • May be of these types: self, family, recognition, recreation, travel. Other "events" are in their own tables but not in this one: Employment, Recorded Programs, Piece Recordings, Concerts, Piece Performances, Broadcasts, Media Events, Masterclasses, Lessons

Category:PersonalEvent Template:PersonalEvent Form:PersonalEvent Special:CargoTables/PersonalEvents

Education

  • fields: InstitutionOrTeacher (Page), Type (Text), Startdate (Date), EndDate (Date), DateRange (StartDate - EndDate), City (Text), Country (Text), Location (Coordinates), Description (Text), DataSource (Page), Status (Boolean)
  • schooling, training, lesson
  • page name=InstitutionOrTeacher Type DateRange
  • Category: Education

Employment and Leadership

  • fields: EmployerOrOrganization (Page), StartDate (Date), EndDate (Date), DateRange (StartDate - EndDate), Role (Text), Description (Text), DataSource (Page), Status (Boolean)
  • page name=EmployerOrOrganization Role
  • include institutional appointments and organizational positions, including boards and committees
  • description includes nature of the work and responsibilities
  • Category: Employment And Leadership

Recorded Programs

  • fields: SeriesTitle (Text), Title (Text), Publisher (Text), PublicationDate (Date), Medium (Text), RecordingVenue (Text), Producer (Text), Pieces (list (,) of Pages (Piece Recordings)), ProgramNotes (list of Pages(Program Notes)), TechnicalNotes (Text), Status (Boolean)
  • page name=Title Publication Date
  • technical notes include details on microphones, method or recording, recording engineer, re-release details
  • details of pieces and performers will be in the corresponding "Piece Recording" records
  • Category: Recorded Program

Piece Recordings

  • fields: Piece (Page), Recording (Page (Recorded Program)), RecordingDate (Date), Performers (List (,) of Pages), DataSources (Text), Status (Boolean)
  • page name="Recording of" Piece "in" Recording
  • put list of performers in same order as list of instruments that is found in the corresponding Piece page; use "conductor" as the instrument for a conductor; if performer is an orchestra, give name of the orchestra as the performer and "orchestra" as the instrument
  • Category:Piece Recording

Program Notes

  • fields: NoteTitle (Page), Program (Page), Author (Text), ProgramNote (Text), Status (Boolean)
  • page name="Program Note on" NoteTitle "in" Program
  • the NoteTitle field will be a page title of a Piece, a Composer, or a Performer; the program field will be a page for Recorded Program or a Concert
  • no separate data source is included since the source is assumed to be the liner notes of the Recorded Program or the printed program for the Concert
  • Category:Program Note

Concerts

  • fields: SeriesTitle (Page), Title (Text), DateTime (Datetime), Venue (Text), Host (Text), Sponsor (Text), Producer (Text), PiecesPerformed (List (,) of Pages (Piece Performances)), ProgramNotes (List (,)of Pages), Recording (Boolean), DataSource (Text), Status (Boolean)
  • details of pieces and performers will be in the corresponding "Piece Performance" and Piece records
  • page name=SeriesTitle"—"Title"—"Date

Category:Concert Template:Concert Form:Concert Special:CargoTables/Concerts

Concert Series

  • fields: ConcertSeries (Page), Organization (Text)
  • on corresponding pages include listing of all concerts in the series

Category:ConcertSeries Template:ConcertSeries Form:ConcertSeries Special:CargoTables/ConcertSeries

Piece Performances

  • fields: Piece (Page), Concert (Page), Date (Date (Concert)), Performers (list (,) of Pages), DataSources (Text), Status (Boolean)
  • page name=Piece "performed at" Concert
  • put list of performers (from Performer category) in same order as list of instruments in the corresponding Piece page; use "conductor" as the instrument for a conductor; if performer is an orchestra, give name of the orchestra as the performer and "orchestra" as the instrument

Category:PiecePerformance Template:PiecePerformance Form:PiecePerformance Special:CargoTables/PiecePerformances

Broadcasts

  • fields: Broadcaster (Text), Channel (Text), SeriesTitle (Page), Title (Text), Date (Date), RecordingDate (Date), RecordingVenue (Text), Host (Text), Producer (Text), Recordings (List (,) of Pages (Piece Recordings)), Status (Boolean)
  • details of pieces and performers will be in the corresponding "Piece Recordings" and Piece records
  • notes will include information about the circumstances and arrangements for the recoding and broadcast
  • Category:Broadcast

Media Events

  • fields: Title (Text), Date (Date), Medium (Text), Host (Text), Description (Text), DataSource (Text), Status (boolean)
  • page name=Title Date
  • could include interviews, panels, talks

Masterclasses

  • fields: Title (Text), StartDate (Date), EndDate (Date), DateRange, Venue (Text), Sponsor (Text), Organizer (Text), Assistants (List (,) of Text), Program (Text), Participants (list (,) of Pages), DataSource (Page), Status (Boolean)
  • page name=Title Venue DateRange

Pieces

  • fields: Composer (Page), Title (Text), Completion Date (Date), Publisher (Text), PublicationDate (Date), Edition (Text), Dedication (Text), Arranger (Page), Editor (Page), Instrumentation (List (;) of Text), Duration (Text), DataSource (text), Status (Boolean)
  • title includes opus or catalogue number
  • page name=Title"&emdash;"Composer

Category:Piece Template:Piece Form:Piece Special:CargoTables/Pieces

Persons

  • fields: Surname (Text), GivenNames (Text), RelationshipToRobertAitken (list (,) of Text), MailingAddress (Text), Telephone (Text), Email (URL), Website (URL), DOB (Date), DOD (Date), DataSource (list (,) of Text), Status (Boolean)
  • relationships include friend, associate, colleague, student, agent, staff, support
  • page name=GivenNames Surname
  • Category:Person

Composers

  • fields: Surname (Text), GivenNames (Text), DOB (Date), PlaceOfBirth (Date), DOD (Date), PlaceOfDeath (Date), Citizenship (list (,) of Text), RelationshipToRobertAitken (list (,) of Text), DataSource (Text), Status (Boolean)
  • relationship to Robert Aitken includes colleague, mentor, teacher, student, associate, friend
  • page name=GivenNames Surname

Category:Composer Template:Composer Form:Composer Special:CargoTables/Composers

Performers

  • fields: Surname (Text), GivenNames (Text), DOB (Date), PlaceOfBirth (Date), DOD (Date), PlaceOfDeath (Date), Citizenship (list (,) of Text), RelationshipToRobertAIitken (list (,) of Text), DataSource (Text), Status (Boolean)
  • relationship to Robert Aitken includes colleague, mentor, teacher, student, associate, friend
  • page name=GivenNames Surname
  • Category:Performer

Category:Performer Template:Performer Form:Performer Special:CargoTables/Performers

Flutes

  • fields: Maker (Text), Type (Text), SerialNumber (Text), Material (Text), DateOfManufacture (Date), DateOfAcquisition (Date), SourceOfAcquisition (Text), DataSource (Text), Status (Boolean)
  • page name=Maker Type SerialNumber

Lessons

  • fields: Student (Page), Date (Date), Location (Text), Focus (Text), DataSource (Text), Status (Boolean)
  • page name=Student--"Lesson" Date

Interviews

  • fields: Interviewee (Page), Interviewer (Page), Date (Date), Format (Text), Processing (Text), Summary (Text), Recording (URL), DataSource (Text), Status (Boolean)
  • page name: Interviewee "interviewed by" Interviewer Date
  • summary would include purpose and scope with key ideas; recording would link to file or be file name
  • page would include transcript of interview (but this would not be in the table)

FluteTeaching

  • fields: Topic (Page), Subtopic (text), Summary (Text), VerifiedByAitken (Boolean), DataSource (Text)
  • page name: Topic--Subtopic
  • Topic would link to a Category page on that topic, the summary would give a capsule description, and VerifiedByAitken is either Yes or No (true or false)
  • Categories: FluteTeaching, "Topic"

Category:FluteTeaching Template:FluteTeaching Form:FluteTeaching Special:CargoTables/FluteTeaching

Toronto Public Library Record Sample

Recording: Lyric Arts Trio recording of concert 1970 [1] (see as an example of record structure)

Queries

Some queries will be included with some pages so that what is displayed may be selected by the reader. Each such page would begin with a description of what the page is and how its contents are generated.

Page Outlines

Concerts

(optional choices: date range, Country)

A listing of concerts, one per line, formatted as follows:

Date | Concert name | City, Country | Hall

(The concert name would have a link to the concert page, which would include details on the concert)

Recordings

A listing of recordings arranged chronologically by date. The title of the recording would have a link to the page on that recording.

(options: chronological order, single listing or grouped by label)

Date | Title of recording | Performers | Medium

Pieces

(optional choices: Composer, for Aitken, NMC commission, date range of composition, ensemble)

A listing of pieces, with link from title to page on that piece

Composer | Title | Date written

Note that a piece may have multiple versions, so it would be good in a concert listing to indicate which version is used.

People

The name of the person would have a link to a page on that person. The page would be a listing alphabetically by surname

Name | Relationship to Robert Aitken (allow multiple relationships)

Masterclasses

A listing of masterclasses. The title of each would have a link to a page on that class

Date range | Title | Location

Displaying Data

See example of Timeline result format. Could provide options for the categories of data to be displayed on the timeline. The popup of entries on the timeline would have links to the related pages.