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.
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.
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.
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 “[-]”.
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”.
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.
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).
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”.]
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.
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.]
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”.
Field | Type | Meaning |
---|---|---|
id | integer | Primary key [-] |
dance_id | integer | Foreign key from table “dance” |
step_id | integer | Foreign key from table “step” |
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”).]
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.
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.
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 |
Field | Type | Meaning |
---|---|---|
id | integer | Primary key [-] |
group_id | integer | Foreign key to table “person” |
person_id | integer | Foreign key to table “person” |
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”.
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) |
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.
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.]
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”.
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.
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 |
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 |
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.
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”.
Field | Type | Meaning |
---|---|---|
id | integer | Primary key [-] |
tune_id | integer | Foreign key from “tunes” table |
alias | string | Alias name |
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.
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) |
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.
Sign in to see recent visitors!