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

DanceData Table Structures

Introduction

This document tries to explain the various tables and fields of the DanceData database by Alan Paterson. The explanations given here are strictly unofficial; there is no guarantee as to their correctness and completeness.

Tables

The various tables appear in alphabetical order. The table and field names are from the “.hdr” files that come with Alan’s textual database dump and will probably have to be adjusted for SQL conformance. My (Anselm’s) personal opinions are added in […].

Update (August 2009): The table and column names and the field order within the tables now conform to the SQL dumps available on http://media.strathspey.org/scddata.

Table “album”

Field Type Meaning
id integer Primary key
name string Name of the album
shortname string Shortened version of “name”
artist_id integer Foreign key from table “people”
lastmod date Last modification of the record
oncd boolean Whether the album was published on CD
onmc boolean … on MC
onlp boolean … on LP
cdcatalogno string Catalog number of the CD (if applicable)
mccatalogno string Catalog number of the MC (if applicable)
lpcatalogno string Catalog number of the LP (if applicable)
verifier string Initials of person who verified the record
verified boolean Whether the record was verified
alphaorder integer ?
isavailable boolean Whether the album is available commercially
productionyear integer Year the album was produced (see album)
ramsayindexverno integer see below
notes string Any notes concerning the album
creationdate date Creation of the record
  • “verified” presumably means that the data in the record was compared to what the actual record says. “verifier” seems to be the person who did the verification – I would guess that “AP” means “Alan Paterson”. [This should really be a foreign key to “people”, or, in the Django-based world, to the User table – but the latter would make the table “unportable”.]

  • [I’m not enthusiastic about the “isavailable” field – on the one hand, the medium may make a difference (which would argue for “isavailablecd”, “isavailablelp”, and “isavailablemc” fields), and on the other hand, the value may mostly be based on hearsay.]

  • The “ramsayindexverno” applies to the Ramsay Index, a publication containing details on SCD recordings that Alan has helped produce. [It isn’t entirely clear to me what this field means; in the actual table its value seems to be either 0, 1, or 2. My conjecture is that “1” means the album shows up in version 1 of the Ramsay Index, “2” means it was added in version 2, and “0” means it is not in the Ramsay Index at all. I get this from looking at the data and noting that the albums at the end (the newest ones) all have “0”, then there’s a bunch of 2s in front of them, and 1s near the start of the file. If anyone can shed light on this (Alan?) that would be great.]

  • The “shortname” is useful for MP3 players and such.

Table “albumsrecordingsmap”

Field Type Meaning
id integer Primary key [-]
album_id integer Foreign key from table “album”
recording_id integer Foreign key from table “recording”
tracknumber integer Track number of the recording on the album

This table links recordings and albums and allows us to find all the recordings on an album, or all the albums a recording occurs on. A track number of “0” means that the actual track number is unknown.

Connoisseurs of database design will note that records in this sort of “intermediary” table does not actually require a primary key. However, Django currently requires this. If you are using the data within another framework, feel free to ignore any primary key fields marked “[-]”.

Table “couples”

Field Type Meaning
id integer Primary key
name string “Couples” description

This table contains various arrangements of dancing people within a set (or “shape”). This includes the obvious “1 couple”, “2 couples” up to “8 couples”, but also “3 persons” or “3 facing 3”. Key 98 is “other” and key 99 is “unknown”.

Table “couplesshape”

Field Type Meaning
id integer Primary key [-]
couples_id integer Foreign key from table “couples”
shape_id integer Foreign key from table “shape”
description string Textual description

This table gives textual names (field “description”) to various arrangements of dancing couples within set shapes. For example, the combination of “5 couples” (“Couples” = 5) and “Square” (“shape” = 4) leads to a “description” of “5C/SQ”, meaning a 5-couple square set (one couple in the centre) as in Spiffin' or The Anniversary Tensome.

Table “dancealias”

Field Type Meaning
id integer Primary key
dance_id integer Foreign key from table “dance”
name string Alias name
ucname string Alias name (in uppercase)
isofficial boolean Whether the alias name is “official”

A record in this table specifies another name for the dance whose primary key in “dance” equals “dance_id”. An “official” alias is published with the dance (such as Broun's Reel for Duke of Perth) while an “unofficial” alias is usually made up for humourous purposes (like The Glens of Angus for The Glens of Angus).

Table “dance”

Field Type Meaning
id integer Primary key
barsperrepeat integer Number of bars for one turn of the dance
name string (Primary) name of the dance
ucname string Version of “name” suitable for sorting
shape_id integer Foreign key from table “shapes”
type_id integer Foreign key from table “dancetype”
couples_id integer Foreign key from table “couples”
devisor_id integer Foreign key from table “person”
verified boolean Whether data was verified
lastmod date Date of last modification
devised date Date the dance was written (if available)
notes string Notes concerning the dance
medleytype_id integer Foreign key from table “medleytype”
progression_id integer Foreign key from table “progression”
url string URL to a description of the dance
creationdate date Date the record was created
  • “barsperrepeat” is the number of bars taken up by one complete turn of the dance – for medleys, this is the sum of all the various parts, so for Cauld Kail (a S16+R16 medley) this is 32 and for Schiehallion (a S64+R64 medley) this is 128.

  • Note that the database does not store whether a dance is “8 times through”, “4 times through” or a “set dance” that goes only once through. This is a recording attribute – for many dances there are recordings with different numbers of turns.

  • “url” is a URL for a description of the dance. The value omits a leading “http://” [which I happen to think is a bad idea]. There is no way of knowing whether the description pointed to here is “official” (many are but many others aren’t). [I think in the long run we should have a “dancesurls” table that can store several URLs per dance, together with “reliability levels”.]

Table “dancesformationsmap”

Field Type Meaning
id integer Primary key [-]
dance_id integer Foreign key from table “dance”
formation_id integer Foreign key from table “formation”
number integer Position of the formation within the dance
instancenum integer Occurrence of the formation within the dance

This table relates formations to dances – we can find out all the dances that contain a particular formation as well as the formations within a single dance. The focus is mostly on being able to locate dances that contain particular formations rather than being able to reconstruct the actual dance from a formations listing.

  • The “number” field gives the position of the formation within the dance. For example, in Duke of Perth, “Turn Corners and Partner” is at position 2 (which is mostly to make clear that it does not start the dance), “Set to and turn Corners” is at position 3 (since it immediately follows the “Turn corners and partner”), and “Reel of three - with corners & crossing” is at position 4. [This does beg the question of what exactly position 1 is to be but generally turning and casting are “small fry” that the database does not bother about.]

  • The “instancenum” field is used to differentiate between several occurrences of the same figure within a dance. An example would be Rory o' More, which features two sets of “Advance and Retire” followed by “Arches” that make up positions 1 to 4. Positions 3 and 4 have “instancenum” set to 2.

Table “dancespublicationsmap”

Field Type Meaning
id integer Primary key [-]
dance_id integer Foreign key from table “dance”
publication_id integer Foreign key from table “publication”
number integer Number of the dance within the publication
priority integer See below

This table relates dances and publications.

  • The “number” can be the actual number of a dance if the dances within the publication are explicitly numbered, or else the page number the dance appears on.

  • If a dance appears in several publications, the “priority” allows us to tell which publication is the most “canonical”. For example, Duke of Perth can be found in various publications including RSCDS book 1, “101 Scottish Country Dances”, the Collins Pocket Reference book, and various editions of “Scottish Country Dances in Diagrams”, a.k.a. Pilling, a.k.a. The Wee Green Book. Of these, the actual RSCDS book is viewed as the most official publication, while the diagrams rate low on the scale. – The rule is that the lower the number, the more official the publication. So all dances that have just been published once have the value 1. [It turns out that in the actual data all the priorities for a dance are distinct but in point of fact I’d say that one Pilling-style diagram is probably as good (or bad) as any other, so it isn’t clear to me why the 7th edition should have a higher priority than the 8th. I would probably put the RSCDS books (book 1 with music, pocket book, and book 1–6 omnibus) at priority 1 and everything graphical – arbitrarily – at 100 or so.]

Table “dancesrecordingsmap”

Field Type Meaning
id integer Primary key [-]
dance_id integer Foreign key from table “dance”
alias_id [1.1] integer Foreign key from table “dancealias” (or NULL)
recording_id integer Foreign key from table “recording”

Changed (schema version 1.1): This table now allows for the fact that sometimes the name of a recording for a dance differs from the actual name of the dance (e.g., “Seton’s Ceilidh Band” is sometimes recorded under the title of “Seaton’s Ceilidh Band”). The way to handle this is to introduce a dance alias (in table “dancealias”) for the variant spelling and put its index into “alias_id”.

Table “dancesstepsmap”

Field Type Meaning
id integer Primary key [-]
dance_id integer Foreign key from table “dance”
step_id integer Foreign key from table “step”

Table “dancestunesmap”

Field Type Meaning
id integer Primary key [-]
dance_id integer Foreign key from table “dance”
tune_id integer Foreign key from table “tune”

[In this table I should really like to see an indication of whether the tune was actually suggested for the dance by the author, whether it was suggested as an alternate, whether the author said “Any good …”, or whether the tune was associated with the dance by popular conviction. For example, several of the older RSCDS books specify two tunes per dance, but the database does not mention which of the two was printed at the top of the page (and is thus normally considered “the original tune”).]

Table “dancetype”

Field Type Meaning
id integer Primary key
name string “Full” name of the dance type
short_name string 1-character abbreviated version of “name”
rectype boolean Whether this is a type of music
dnctype boolean Whether this is a type of dance
dialogorder integer ?

This table really contains the types of dances as well as musical genres. For example, “Reel” may be both a type of dance and a musical genre, but “Listening” is not a type of dance. (We need the latter because some albums include “listening” sets that are not intended for dancing.)

Note that according to the database, “Hornpipe” is just a musical genre, not a type of dance. As far as dancing is concerned, all hornpipes are reels.

“Other” has key 50 while “unknown” has key 99.

Table “formationstepmap”

Field Type Meaning
id integer Primary key [-]
formation_id integer Foreign key to table “formation”
type_id integer Foreign key to table “dancetype”
step_id integer Foreign key to table “step”

This table lets us find the steps that particular formations involve. For example, “Advance and Retire” in jigs and reels is done using skip-change of step but in strathspeys it requires strathspey travelling step. Hence, to find the steps for a formation, you need the formation’s primary key as well as a dance type, e.g., from the dance that is currently under consideration.

Table “formation”

Field Type Meaning
id integer Primary key [-]
napiername string Abbreviated name as in Napier’s Index
name string Longhand name
search_id string Augmented name for searching

Table “group”

Field Type Meaning
id integer Primary key [-]
group_id integer Foreign key to table “person”
person_id integer Foreign key to table “person”

Table “medleytype”

Field Type Meaning
id integer Primary key
description string Textual description of the bar arrangement
sortstring string Description formatted suitable for sorting

“description” is what one would want to print on a CD inlay or similar place – things like “S16+R16” or “S48+J48”. On the other hand, “sortstring” has the various bits padded out to three digits and no pluses in between, like “S016R016” and “S048J048”, so it is easy to sort medleys according to the rhythm and length of the parts.

Key 9999 is just “Medley”.

Table “person”

Field Type Meaning
id integer Primary key
name string Full name (or whatever is known) (see below)
shortname string Abbreviated name (see below)
displayname string Name for display (see below)
location string Location in the world (city, country)
email string E-mail address (empty if unavailable)
isdev boolean Whether the person devises dances
ispub boolean Whether the person puts out publications
iscmp boolean Whether the person composes tunes
ismus boolean Whether the person plays music for dancing
sameas_id integer Foreign key from table “people”
byear integer Year of birth (0 if unknown)
dyear integer Year of death (0 if unknown/alive)
lastmod date Date of last modification

This table collects information about “people”, where this term is taken very loosely – it includes dance groups, musical bands, and co-authorships of publications.

  • The “name” always starts with the surname, possibly followed (after a comma) by first and middle names and/or initials.

  • The “shortname” is either empty (in which case one should resort to using one of the other names) or else gives an abbreviated version of the “name”. This is mostly useful for band names, where instead of “The Robert McGillivray Scottish Country Dance Band” one would want to put “Bob McGillivray” in places where there isn’t enough room.

  • The “displayname” gives the first names/initials first, then the surname. This obviates the need to guess the actual name from the inverted version in “name”, which may be troublesome with names like “Johnstone, Muriel’s Band” that come out as “Muriel’s Band Johnstone” if handled naïvely.

  • The “sameas_id” foreign key is used to deal with people who change names, e.g., because they get married. For example, Barbara Bouwsma (key 882) is now Barbara McOwen (key 1773). It isn’t clear if there is a rule governing which primary key should be in the “sameas_id” field, but if there are several records describing the same person they should all have “sameas” set to something non-zero (1773, in Barbara’s case). The value zero means that there is just one record for that person in the database.

Table “phrasing”

Field Type Meaning
id integer Primary key
name string Description of the phrasing

This table describes various musical phrasings, for use in the “recordings” table. The “name” entries are of the form “AABB” or “ABAB”, where A and B (usually) stand for different eight-bar musical phrases. If the number of bars is not equal to eight, it can be appended to the letter, so The Princess Royal is “AAB12”.

[Actually, even for recordings, the second tune may use a different phrasing from the first and so on. Many musicians try to pick their tunes such that this does not happen too often, but even so it is not an uncommon occurrence.]

Table “progression”

Field Type Meaning
id integer Primary key
name string Description of the progression

This table lists various “progressions” by means of stating the positions of the couples involved after one turn of the dance. For example, “2413” is a typical progression in a four-couple dance.

Interestingly, the most common progression – 2134 – does not occur in the list [it is probably uninteresting]. The value 0 as a foreign key pretty much amounts to a progression name of “2134”. [I still think this ought to be fixed.]

  • Primary key 30 is “other”.

  • Primary key 3 is “set”, which applies to dances that only go once through such as The Eightsome Reel or Johnnie Walker [apparently not all such dances are marked thus].

  • Primary key 4 is “chng” [“change”], which covers dances where after each turn one changes partner, like Caddam Wood or Nighean Donn.

  • Primary key 8 is “fugue”, which according to the dances that use it should probably be called “canon”. (A canon is a dance where couple 2 start their turn as dancing couple before couple 1 is finished – usually after half the dance. A fugue is a dance where, within the same turn, various dancers (couples or other) start at intervals performing (loosely) the same movement – it doesn’t in fact impinge on the progression scheme of a dance.)

  • Primary key 23 is “Spec”, which presumably means “special”.

Table “publication”

Field Type Meaning
id integer Primary key
name string “Full” name of the publication
shortname string Abbreviated name of the publication
devisor_id integer Foreign key from “persone” table
lastmod date Date of last modification
hasdances boolean Whether the publication contains dances
hastunes boolean Whether the publication contains tunes
pagenumbering boolean See below
url string URL where the publication is available
onpaper boolean Whether the publ is available on paper
creationdate date Creation date of the record
  • “devisor_id” points to the person or entity that published the publication, and that may or may not be associated with any of the actual dances or tunes within it.

  • “pagenumbering” is true for publications that do not number individual dances but do number pages. It changes the interpretation of the “number” field in the “dancespublicationsmap” table.

  • The “shortname” field is suitable, for example, for use in ball programmes.

  • As far as the “url” field is concerned, see the “dances” table for a discussion of the caveats.

Table “recording”

Field Type Meaning
id integer Primary key
name string Full name of the recording
artist_id integer Foreign key from “person” table
type_id integer Foreign key from “dancetype” table
repetitions integer Number of turns of the dance recorded
barsperrepeat integer Number of bars per turn of the dance
medleytype_id integer Foreign key from “medleytype” table
phrasing_id integer Foreign key from “phrasing” table
playingseconds integer Duration of the recording, in seconds
twochords boolean Whether the recording starts with 2 chords
notes string Notes concerning this recording

Table “shapeprogressionsmap”

Field Type Meaning
id integer Primary key [-]
shape_id integer Foreign key from “shape” table
progression_id integer Foreign key from “progression” table
couples_id integer Foreign key from “couples” table

Table “shape”

Field Type Meaning
id integer Primary key
name string Full name of the shape
shortname string Abbreviated name of the shape

A “shape” gives the number and arrangement of dancers required when forming sets. This includes longwise as well as square sets, round-the-room dances, and so on. It usually goes together with the “couples” table to describe the geometry of a dance set.

Table “step”

Field Type Meaning
id integer Primary key
name string Full name of the step
shortname string Abbreviated name of the step

This is mostly used to associate particular steps with dances and formations. Note that key=99 is “Other”.

Table “tunealias”

Field Type Meaning
id integer Primary key [-]
tune_id integer Foreign key from “tunes” table
alias string Alias name

Table “tune”

Field Type Meaning
id integer Primary key
name string Full name of the tune
composer_id integer Foreign key from “person” table
url string URL for the tune
notes string Notes concerning the tune
creationdate date Date the record was created
  • [This could of course be greatly extended and improved.]

  • See the discussion of the “url” field in the description of the “dances” table for the caveats.

Table “tunespublicationsmap”

Field Type Meaning
id integer Primary key [-]
tune_id number Foreign key from “tune” table
publication_id number Foreign key from “publication” table
main boolean Whether the publication is the main source
number number Number of the tune within the publ (or 0)

Table “tunesrecordingsmap”

Field Type Meaning
id integer Primary key
tune_id number Foreign key from “tune” table
alias_id number Foreign key from “tunealias” table (or NULL)
recording_id number Foreign key from “recording” table
number number Number of the tune within the publ (or 0)

This table lets us figure out which tunes make up a recording and in what order.

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