Cecilia's List: database structure stuff

[artsyhonker]

Things I want to track about music:

Title/First line of text
Composer: Last name, first name, initials, birth year, death year, nationality, website, e-mail, role, link to contact composer, link to composer's website, notes
Arranger: all the info from Composer.
Language
Lyricist
Translator
Date of composition/publication
Publisher (if applicable)
Voicing
Instrumentation
Genre (e.g. hymn, chant, anthem, canticle, responses, voluntary...)
Metre (for hymns)
Tune name (for hymns)
Duration (in time)
Duration (in verses, for hymns)
Difficulty
Hymnals the work is published in
Anthologies the work is published in
Url to order a deadtree copy
url to buy a download
url to a free download, if any legal
url to contact composer
Liturgical context: seasons, saints, services, themes, where in the service it might fit,
Scriptural references (this is fairly complicated because a piece might be relevant to one verse or to a range of them and it might skip some within that range, but at least someone has numbered the chapters and verses already, thank you Dominicans; I almost want to do this with a link to bible.oremus.org because that is a sensible site)
Lectionary Date (there are three years worth of these)
Related works (e.g. a Magnificat may be linked to a Nunc Dimittis)

Some of these are one-to-one relationships, some of them are one-to-many. I don't really get how to do the one-to-many thing, yet.

I think I need the following tables:
Works
People
Hymnals
Urls
Liturgical context
Scriptural context
Lectionary Date
Relationships between works

But, I am not quite sure what I am doing...

Comments

[siderea]

So, I'm not saying this to be discouraging, but because I want to give you advanced notice: your database is going to be a hell of a project. Not only is it already a hell of a project as you have it spec'ed out here, I can already see – having done some thinking on a score-tracking db myself – some places you're going to discover it's thin and in need of further elaboration.

I don't understand from what you've said whether you're planning on this having a web interface, or whether you're planning on slapping this together in a desktop app for your own use, that then you output something like blog posts from. If you're intending on having this on the web as a user searchable db, you have a really enormous development project ahead of you.

A rule of thumb I used from back in the day when I costed out complicated web development projects: one week per table. You have eight weeks of full-time web development so far – and while I think you can ditch the Urls table, I suspect you've missed some other ones.

Your previous post said you anticipate launching in November. My estimate says that if you start a professional building this on Sept 4th, full time, and have no more tables than you think you do right now, and absolutely nothing goes wrong, you'll have something ready by Hallowe'en. I would put the odds of that estimate slipping at, oh, 90%.

[artsyhonker]

In haste:

Desktop, and I'll be outputting blog posts and eventually a newsletter.

Launch in November is going to be pretty lightweight: probably 10 composers, and organised seasonally.

[artsyhonker]

Longer response now that I have some time:

I am not discouraged, and I realise that the scale of what I am trying to do here is a bit ridiculous.

I expect not to have full development done for... well, probably a couple of years rather than months or weeks. What I really need is an actual music librarian who is also a professional database wrangler, and preferably also involved in church music in some way. But I also want to get started sooner rather than later, because something is better than nothing.

The rest of this comment went rather train-of-thought on me and is probably a little bit too long, but I've written it now, so may as well leave it.

Most resources that people use for choosing music for liturgy are laid out in terms of the liturgical calendar, or sometimes just with rough seasons, because when people are planning this stuff they're planning things that happen at a certain time in that context. Secondary to this there are some (pretty incomplete) resources with thematic or scriptural listings, but mostly people who are planning music for more than one service of a Sunday are relying on their own experience and associations rather than using a planning aid. I'm not sure how churches that ignore the lectionary do it, but they're a] not really my scene (sorry, "outside my target audience") b] ironically sometimes have better representation of women in music anyway.

I don't necessarily need something that's entirely user-searchable, though a website with the information in plain text automagically will be if people want to find, I dunno, a season and a particular lectionary reading; Google will let them do that. I just need to present the information in a way that's useful to people doing the planning, who are thinking "Oh, it's the Sunday next before Lent in Year C, so we have such-and-such readings, what music is out there?" and then factoring in a whole bunch of other stuff too. If I format the pages so that it's easy to see things like the voicing, which (if any) instruments are required, the duration, the difficulty and so on, and also have links to where the music can be purchased/heard/downloaded, I am already literally doing better than the most commonly used resource out there in the UK (it's in deadtree, so no links), which I think is probably also the best resource in the UK and probably internationally (I know it gets used in Canada and the US, and various other English-speaking places).

And if, to start with, the granularity is rough ("Lent" rather than individual Sundays within Lent; "Eastertide" rather than individual Sundays within Eastertide; a page for Evensong, rather than one for Versicles and Responses, one for Canticles, and one for Psalms), that's not a huge problem because to start with there won't be all that much content.

What I want to do in time for the launch is to have static pages with well-curated suggestions for the main seasons, and a wide enough range of composers that it doesn't look like it's just me promoting my own work. Again, going by the existing resources, and bearing in mind this is supposed to be a supplement rather than a replacement? I need ten composers. I definitely know more than ten living women writing church music, and if I'm stuck I can always include some of the dead ones. (It would be a disservice not to include the dead ones, in fact. But they aren't likely to re-tweet my blog posts, so there is going to be at least some bias toward the living for a little while.)

After that -- regular blog posts about issues that affect women in church music, and highlighting new work, interviews with composers, guest blog posts even... the but main thing will be to be regular about it. I want to be working a month ahead on this if I possibly can, which means six weeks really because launching at the end of November runs into Christmas, which means if I'm going to post 3x/week then ideally I want to have eighteen posts pretty much lined up before I start. That's... going to take some doing. But some of the posts can be very short: "Here is a YouTube recording of a piece by so-and-so" is not too hard.

In the background I'll be sorting out what on earth the database needs to be doing, and adding more composers. Yes, this is huge, but it is also not something I am doing entirely on my own. My spouse is more a spreadsheet guy than a database guy, but I'm much more concerned about him not understanding some item of musical relevance than not being able to manage the database part of it.

Eventually, as I have more data, I envision some of the blog posts starting to look like "Here is a whole bunch of music that would be ideal for this Sunday" and get a tag with the lectionary date and with the readings. And eventually I hope to have enough to be doing one of those a week, and eventually sometime after that I hope to get far enough ahead on those to be able to say "hey, if it would help your planning to have these things sent in batches every quarter, another quarter in advance, that's £20/year" or whatever.

But -- it could develop completely differently. It could end up as a database of women writing church music, and the planning resources I mentioned earlier in this comment could start using it instead of only pointing to the same six or eight women all the time. I could decide to crowdfund the technical development side of it. Something else could happen. I don't entirely know. And while I don't want to put too many stumbling blocks in the path of Future Me by doing a database badly from the outset, I also recognise that I simply don't know enough about how it's going to develop to be able to do it perfectly, so I may as well just start, and accept that at various stages in the project, the amount of data or what I want to do with it will outstrip whatever method I've used so far and I'll need to rethink things and spend time, money or both fixing it. At least I'm not using a card catalogue. (I did consider it.)

There's also an element of curation in it. To an extent, yes, I want to promote the music of women composers; but I also want to do a certain amount of quality control, otherwise it won't be a useful resource. There is music out there which is...sacred, but not really intended or suited for use in liturgical contexts, and including too much of that just makes speedbumps for people without really being part of the scene I'm trying to influence. Similarly, there's the whole Contemporary Christian Music scene (worship bands with guitars, often found in evangelical mega-churches), which again isn't really the scene I'm trying to influence, though including some of the songs which lend themselves to congregational singing won't be the end of the world. After ruling most of that out, there's also a lot that simply isn't very good, which is the worst for me to include.

I don't want too much that's too hard or it will be useless to anyone who isn't a cathedral, though I need to include enough at that grade for it to be useful for churches with very good choirs who can handle that kind of repertoire. But most churches have smallish amateur choirs and are resistant to new music in the first place, so including something that will suit those choirs is sensible. Most churches do sing congregational hymns and it's sensible to include examples of good congregational hymnody. Psalm chants can have their own page and are extremely easy to slot into existing liturgy because within types, it's usual that any psalm can be sung to any chant. I have a friend who is going to help with the organ voluntaries side of things (or possibly do her own page for those, in which case I will link to it).