Database stuff
Jun. 16th, 2017 02:05 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
artsyhonkerOK, for Cecilia's List I will need a database.
That is: the volume of information will get really stupid realyl fast if I try to do it without a database of some sort.
For now, I am happy enough with a database where I ask it for a report (which will include some html formatting) and then copy and paste that into a Wordpress page or blog post. Really! This is because this introduces a layer of human proofreading, and I like human proofreading.
It is over a decade since I did any kind of work with databases at all, and that was... rudimentary, to say the least. So I don't really know what I'm doing.
I know I want a list of works, with various attributes: Composer, voicing, instrumentation and so on.
I know I want some of those attributes to be in tables of their own. For example, if a composer changes their name or their website, I don't want to have to edit 30 records for those changes to show elsewhere in the database. (Reflecting the changes on the website can wait until a quarterly update if they are extensive, or be done by hand if they are not.)
I want to be able to do my data entry using a form, and I want the form to give me meaningful options for things like composers: yes, each will have a unique ID, but I'm not going to memorise that.
Currently I'm using LibreOffice Base, because getting it to run on my machine was trivial and free. But I'm struggling with linking the tables: when I make a form I can't figure out how to have a drop-down menu to choose composers, for example, and whenver I use the form to add a new record to the "Works" table, it adds a new composer in the "Composers" table, even though I'm using an existing one. This is unhelpful!
So, I'm struggling with this one-to-many relational database thing. I mean, each work is only going to have one composer (there are few enough collaborative works that I am happy to deal with those on a case-by-case basis); but a composer might have many works, and when I enter the information for a work I want to be able to choose a composer OR add a new one. And I just do not have the technical skills to get from here to there.
But that's not the only sort of linked table I need. I will be, to start with, primarily presenting the information by season, feast day, personality (works in honour of the BVM, or St Paul, and so on), service (Evensong? Eucharist? Wedding?), and theme (so far I only have Extraordinary Times in this -- things like disasters -- but it should eventually expand to be a proper scriptural reference index). In time I want to organise it in a lectionary-based manner, at which point the scriptural reference index starts to be helpful: where the readings for such-and-such a day are so-and-so, I can ask the database which works are relevant. (These are going to be one-off blog posts/e-mails, not static pages that absolutely have to be kept up to date.)
So I guess I need a table that is seasons and feast days, and a way to indicate in the works table if the work is suitable for more than one (e.g. things for Good Friday are often, though not always, also suitable for Holy Cross Day; things suitable for Corpus Christi are usually suitable for a Eucharist). I need a table for services, a table for personalities, and a table for themes.
I'm less convinced that each of these should be done with a linked table rather than just having several hundred columns with tickyboxen in them. Something suitable for Easter is not going to stop being suitable for Easter, and the Easter things may have nothing else in common. Admittedly the several hundred columns would be a pain, but this is what forms are for, right? Except even if I put them in a different table I still don't know how to design the form so that I get a handy drop-down (or something) rather than something I have to scroll through forever.
Why not just have three columns for seasons, labelled Season 1, Season 2, Season 3? And type text into them; and then do similar for theme and so on. It would still let me run a report on anything that has "Easter" in those fields. Belovedhairyears was convinced last night that I should definitely be using a linked table, but he was unable to explain it to me in sufficiently simple terms that I could understand why.
Further to all of this: at the moment I am talking about creating reports and then hand pasting the information into Wordpress pages. I don't have a big problem with this for the scale I'm working at: 20-ish seasons/feasts, various saints etc (potentially infinite but probably going to be mostly limited to the 12 apostles, Our Lady, and of course St Cecilia), and blog posts for the week-to-week lectionary stuff. But it's conceivable that at some point in the future I might want to turn this into some kind of thing where users can edit the database themselves, at which point I need it to be an online database where the web pages get built on the fly, sortof. I think. Maybe. Is that a thing? It must be a thing.
My temptation, of course, is just to leap in all gangbusters and do this without a database; but that kindof doesn't bear thinking about. I mean, I guess if every composer had a page, and every work had a page, and every voicing had a page, and so on and so forth, then people could look up whatever they want to look up by whatever attribute they want to look up, and the website itself would be several hundred pages and a whole terrible mess of links. It would be hellish to maintain, but everyone could look up anything.
But... search functions already exist, so people can still look for everything that's voiced SA if they really want to. And, well, I am mostly happy to let composers do their own works list pages, rather than have my own that I try to keep up to date, and that they try to keep track of and end up nagging me about. So I only really want one way to contact a composer and it should be their own website which they can maintain themselves. I am pretty sure that most of my target audience, faced with a Crux fidelis setting in English, will be able to tell whether it's suitable for, say, a Harvest Evensong (spoiler: NO). Lots of these attributes aren't really things that are the primary consideration when planning music. I know, because I've done it. I don't start from "Bach would be nice this week," I start from non-musical considerations.
The main problem is people going "okay, Transfiguration is in four months, do we have any music by women? er... er... let's stick to the usual shall we?" and then doing what they did last year or the year before, because they aren't familiar with repertoire writtne by women becauseof a massive old-boys' network it isn't something they've been exposed to. That's the process that I want to change. The ideal use case is that they look up the season (or the lectionary stuff or the saint or the theme) and get a list of decent resources, with enough information for them to decide which things to investigate further (by downloading the work, ordering a copy, or contacting the composer to request a perusal copy). This isn't even meant to replace their existing resources, it's more of a supplement, so anything more complicated than that is probably going to be offputting.
Er, are any of you good at databases? I think Zair may be able to help me, but I'd like to get a better understanding of what I actually want before I throw myself at another helpful friend.
That is: the volume of information will get really stupid realyl fast if I try to do it without a database of some sort.
For now, I am happy enough with a database where I ask it for a report (which will include some html formatting) and then copy and paste that into a Wordpress page or blog post. Really! This is because this introduces a layer of human proofreading, and I like human proofreading.
It is over a decade since I did any kind of work with databases at all, and that was... rudimentary, to say the least. So I don't really know what I'm doing.
I know I want a list of works, with various attributes: Composer, voicing, instrumentation and so on.
I know I want some of those attributes to be in tables of their own. For example, if a composer changes their name or their website, I don't want to have to edit 30 records for those changes to show elsewhere in the database. (Reflecting the changes on the website can wait until a quarterly update if they are extensive, or be done by hand if they are not.)
I want to be able to do my data entry using a form, and I want the form to give me meaningful options for things like composers: yes, each will have a unique ID, but I'm not going to memorise that.
Currently I'm using LibreOffice Base, because getting it to run on my machine was trivial and free. But I'm struggling with linking the tables: when I make a form I can't figure out how to have a drop-down menu to choose composers, for example, and whenver I use the form to add a new record to the "Works" table, it adds a new composer in the "Composers" table, even though I'm using an existing one. This is unhelpful!
So, I'm struggling with this one-to-many relational database thing. I mean, each work is only going to have one composer (there are few enough collaborative works that I am happy to deal with those on a case-by-case basis); but a composer might have many works, and when I enter the information for a work I want to be able to choose a composer OR add a new one. And I just do not have the technical skills to get from here to there.
But that's not the only sort of linked table I need. I will be, to start with, primarily presenting the information by season, feast day, personality (works in honour of the BVM, or St Paul, and so on), service (Evensong? Eucharist? Wedding?), and theme (so far I only have Extraordinary Times in this -- things like disasters -- but it should eventually expand to be a proper scriptural reference index). In time I want to organise it in a lectionary-based manner, at which point the scriptural reference index starts to be helpful: where the readings for such-and-such a day are so-and-so, I can ask the database which works are relevant. (These are going to be one-off blog posts/e-mails, not static pages that absolutely have to be kept up to date.)
So I guess I need a table that is seasons and feast days, and a way to indicate in the works table if the work is suitable for more than one (e.g. things for Good Friday are often, though not always, also suitable for Holy Cross Day; things suitable for Corpus Christi are usually suitable for a Eucharist). I need a table for services, a table for personalities, and a table for themes.
I'm less convinced that each of these should be done with a linked table rather than just having several hundred columns with tickyboxen in them. Something suitable for Easter is not going to stop being suitable for Easter, and the Easter things may have nothing else in common. Admittedly the several hundred columns would be a pain, but this is what forms are for, right? Except even if I put them in a different table I still don't know how to design the form so that I get a handy drop-down (or something) rather than something I have to scroll through forever.
Why not just have three columns for seasons, labelled Season 1, Season 2, Season 3? And type text into them; and then do similar for theme and so on. It would still let me run a report on anything that has "Easter" in those fields. Beloved
hairyears was convinced last night that I should definitely be using a linked table, but he was unable to explain it to me in sufficiently simple terms that I could understand why. Further to all of this: at the moment I am talking about creating reports and then hand pasting the information into Wordpress pages. I don't have a big problem with this for the scale I'm working at: 20-ish seasons/feasts, various saints etc (potentially infinite but probably going to be mostly limited to the 12 apostles, Our Lady, and of course St Cecilia), and blog posts for the week-to-week lectionary stuff. But it's conceivable that at some point in the future I might want to turn this into some kind of thing where users can edit the database themselves, at which point I need it to be an online database where the web pages get built on the fly, sortof. I think. Maybe. Is that a thing? It must be a thing.
My temptation, of course, is just to leap in all gangbusters and do this without a database; but that kindof doesn't bear thinking about. I mean, I guess if every composer had a page, and every work had a page, and every voicing had a page, and so on and so forth, then people could look up whatever they want to look up by whatever attribute they want to look up, and the website itself would be several hundred pages and a whole terrible mess of links. It would be hellish to maintain, but everyone could look up anything.
But... search functions already exist, so people can still look for everything that's voiced SA if they really want to. And, well, I am mostly happy to let composers do their own works list pages, rather than have my own that I try to keep up to date, and that they try to keep track of and end up nagging me about. So I only really want one way to contact a composer and it should be their own website which they can maintain themselves. I am pretty sure that most of my target audience, faced with a Crux fidelis setting in English, will be able to tell whether it's suitable for, say, a Harvest Evensong (spoiler: NO). Lots of these attributes aren't really things that are the primary consideration when planning music. I know, because I've done it. I don't start from "Bach would be nice this week," I start from non-musical considerations.
The main problem is people going "okay, Transfiguration is in four months, do we have any music by women? er... er... let's stick to the usual shall we?" and then doing what they did last year or the year before, because they aren't familiar with repertoire writtne by women because
Er, are any of you good at databases? I think Zair may be able to help me, but I'd like to get a better understanding of what I actually want before I throw myself at another helpful friend.