We're using cookies to make this site more secure, featureful and efficient.

DanceData Table Structures: Proposed Changes


This document outlines some changes to the DanceData table structures that I would consider worth trying. Please feel free to comment on these changes on the dancedata-friends@strathspey.org mailing list or to myself at anselm@strathspey.org.

Change “album” table for more flexible media representation

Iain Boyd suggested to extend the “album” table to be able to represent EPs and 78s on top of MCs, LPs, and CDs. Rather than add fields to the table I propose creating a new table “albumsmedia” like

Field Type Meaning
id integer Primary key [-]
album_id integer Foreign key from table “albums”
media_id integer Foreign key from table “mediatypes”
catalognumber string Catalog number according to the publisher
year integer Production year
available boolean Whether medium is available new

together with a table “mediatypes” like

Field Type Meaning
id integer Primary key
name string “Compact disc” and so on
abbreviation string “CD”, “MC”, and so on

and removing the “on_cd”, …, “CDcatalgno”, “is_available”, and “production_year” fields from the “albums” table. Note: The value of the “available” field can be TRUE, FALSE or NULL (meaning “unknown”).

Currently the database does not distinguish between different “recommended tunes” for dances. In practice, some dance devisors may suggest more than one tune for a dance (e.g., in various RSCDS books), a dance may be published with a well-known tune but may acquire its own tune later (e.g., The Black Mountain Reel), or a dance may acquire a customary tune because the official tune is no good (e.g., The Montgomeries' Rant or Cadgers in the Canongate).

The suggestion is to change the “dancestunesmap” table like so:

Field Type Meaning
id integer Primary key [-]
dance_id integer Foreign key from table “dance”
tune_id integer Foreign key from table “tune”
priority integer (see below)
comment string Explanatory comment
  • The “priority” field states how common the tune in question is as the dance’s original tune, with lower numbers expressing higher priorities. Usually the tune published with the dance should have the lowest number (highest priority), unless (as in the case of The Montgomeries' Rant) another tune is more popular.

  • The “comment” field can be used to specify in greater detail why the tune in question has the stated priority. This is not mandatory.

  • It would make sense to introduce special “tunes” by the name of “Any good strathspey”, “Any good reel”, etc., so the database can distinguish between dances with no stipulation as to music at all and dances with a suggestion of “anything”. (The difference between the two is that in the latter case the dance devisor explicitly doesn’t care, while in the former the dance devisor may or may not have had a specific tune in mind but that preference, if any, is not stated in the dance instructions.) This would also allow us to express the case of “Music: Such-and-such or any good reel”. For “any good …”-type dances, the comment field can be helpful to encode any guidelines the dance description may include as to tune selection, as in “Any good slow-air-type strathspey”.

The example cases mentioned above could then be modelled thus:

<Waverley> <The Lawland Lads …> 1 <Waverley> <Colonel Ridley’s Quickstep> 2 <Black Mountain Reel> <Black Mountain Reel> 1 recommended tune <Black Mountain Reel> <Trumpet Hornpipe> 2 originally published <Montgomeries’ Rant> <Lady Montgomerie> 1 customary tune <Montgomeries’ Rant> <Lord Eglintoune> 2 official original

Allow several URLs for dance descriptions, tunes and publications

I propose replacing the “url” field in the “dance” table by a separate table, “danceurl”, like

Field Type Meaning
id integer Primary key [-]
dance_id integer Foreign key from table “dances”
url string Uniform resource locator
priority integer (see below)
  • URLs will be stored in full RFC3986 format, i.e., the leading “http://” will not be omitted.

  • The “priority” field allows us to distinguish between “official” descriptions put out or approved by the dance deviser, and informal descriptions put out by others. E.g., a value of 1 could denote an official description, and higher values could stand for third-party sources. See also the discussion of “priority” in the description of the “dancespublicationsmap” in the DanceData format description.

All other tables with an “url” field, namely “tune” and “publication”, should be changed/extended correspondingly.

Recently seen

Sign in to see recent visitors!

You see things, and you say »Why?« But I dream things that never were, and say »Why not?«
– George Bernard Shaw