Databases and psalms

[Ploutos]

Does anybody here have experience with database apps like Access? In my case, I'm using LibreOffice Database. Understanding of such programs has long eluded me, but something clicked recently, and I think I understand at least the basics.

I'm working on a pet project that I may or may not see through to completion - going through my psalters and hymnals, compiling tunes that I like and listing tunes used for each psalm in different books. Now that I've grasped, at least in my head, the very simplest basics of relational data structuring, it's a fun way to pass the occasional free moment here and there. I'm getting used to the idea that the dataset itself is not for visibility, as opposed to a spreadsheet. The idea of queries is pretty neat - given just a minute or two, I could pull up a list of every Long Meter tune in a minor key, or every tune associated with Psalm 36 across a dozen different psalters (assuming I get that far).

I wonder if anybody else has attempted such a project.

 

[hammondjones]

I have only used SQLite db for a local db (in my case or was for school). That would be a good route if you want to really get into relational databases, designing a schema, db normalization, primary and foreign keys, etc., but Access would be faster to start for sure.

 

[Ploutos]

I have only used SQLite db for a local db (in my case or was for school). That would be a good route if you want to really get into relational databases, designing a schema, db normalization, primary and foreign keys, etc., but Access would be faster to start for sure.

Yeah, I'm not smart enough (or don't have the bandwidth) to jump straight into SQL at that level.

LibreOffice is the FOSS version of Microsoft Office - maybe a bit finicky, but more or less the same in terms of ease of use and accessibility.

 

[Afterthought]

I've worked with databases, though not Access. I once tried doing a smaller version of this Psalms project (cross-referencing tunes in the Book of Psalms for Singing with the tunes in the Scottish Psalmody), but never finished and used Excel.

 

[Ransom]

I wonder if anybody else has attempted such a project.

Hi, JP - I have no experience or skills with the technological side of things, but I am currently in the middle of a similar compilation myself. Right now, I just have everything on a Google Doc, and it's relatively disorganized. But please let me know if you learn ways to keep tabs on your project, as I would be eager to learn as well. I would like to turn it into a book/binder for my family to use one day (with proper copyright permissions).

 

[Edward]

I had some exposure to Access when helping my daughter with a high school assignment using it. I'd be starting from scratch if I tried to do anything with it at this point.

 

[1689owen]

I'd say, if you need it to be fully customizable, a db is a good option. Go with whatever db you are familiar with. Have fun and enjoy working yourself through implementing psalms on your own.

For an easier start, there are online tools for similar things, were you can implement your categories. Most of them can do pretty more (and at some point they might not do what you want). I think about planningcenter online (use the right tags for the songs) or the like.

 

[Ploutos]

I've tested some queries and thus far, it seems to be doing what I want with a relatively small dataset. I've started similar projects in Excel as well, because I'm decent with spreadsheets at a basic level; but the problem with doing this in Excel has always been three-fold. First, if I do just a dozen psalters and hymnals, the number of entries and distinct tunes will probably be in the low thousands; second, there would be a fair amount of data repetition; three, sorting to get the exact information I want could be challenging. So it would be a very large spreadsheet to manage.

With the database program, I can create separate tables for fields that are likely to be repeated. So I have a table with a list of meters, a table with a list of composers, a table with the mode (meaning, whether it's in a major key or a minor key), etc. Then I have a table with tune names, and I can link to those other tables for entering in attributes of the tune name. Lastly, I have a table with entries from hymnals and psalters, with the tune name and the associated psalm.

If it works right - if I structure the data correctly, I could run queries like the following:
- all minor-key tunes in common meter or common meter double
- all tunes that have been used with Psalm 18
- all the psalms that have been set to the tune "Amsterdam"
- all tunes in 10.10.10.10 in 3/4 time
- all minor key tunes with a melodic range of less than an octave

I think it would be a pretty cool project when completed. I would have a list of tunes that I've hand-selected for their musical qualities; and I would be able to tell if there is a tradition of certain psalm/tune pairings (an obvious example would be the traditional pairing of Old 100th with, er, Psalm 100).

Of course, it also entails manual data entry for a couple thousand selections, which is why I say I'm not sure if I'll see it through or not.

 

[Afterthought]

Of course, it also entails manual data entry for a couple thousand selections, which is why I say I'm not sure if I'll see it through or not.

That is always the pain point! I wonder if AI could speed it up, e.g., Claude cowork app or something, maybe after having Claude pull the data from various sources like images.

You might know of hymnary.org which does some of what you want done and so could be a data source.

It would be pretty cool!

 

[Theoretical]

Honestly, perplexity computer comes to mind for something like this. The real problem is that you won't be necessarily able to maintain it well if/when it breaks but it's not bad to let it build the latticework that you've defined. I tried some interesting projects in other areas I knew and was impressed with the results.

 

[WithinThePale]

I'd be inclined to use Python and Pandas to manipulate that sort of information, and store everything in flat Excel or csv sheets

 

[Edward]

I'd be inclined to use Python and Pandas

And one could probably use AI to get the basic code started.

 

[Kinghezy]

Of course, it also entails manual data entry for a couple thousand selections, which is why I say I'm not sure if I'll see it through or not.

If you have these selections noted somewhere, using excel to generate insert statements has proven speedy at work (e.g. https://stackoverflow.com/a/16819629).

 

[omniavanitas]

If you're not married to the idea of a database, you could try Obsidian. It uses markdown, and you can add backlinks and hashtags that link things together. It also has a Git plugin, so you can keep everything synchronized. It also has a graph view that shows all the connections between everything. I use it to link my Bible notes to different topics.

If you wanna stick with a DB, I'd recommend PostgreSQL. Multiple GUI options, and it's FOSS. If you ever want to deploy it, it has great support whether you want to self host or use a cloud provider. It also has vector support for RAG if you want to implement AI into your workflow.

 

[Ploutos]

That is always the pain point! I wonder if AI could speed it up, e.g., Claude cowork app or something, maybe after having Claude pull the data from various sources like images.

You might know of hymnary.org which does some of what you want done and so could be a data source.

It would be pretty cool!

I am familiar with Hymnary - that's an interesting idea!

I'd be inclined to use Python and Pandas to manipulate that sort of information, and store everything in flat Excel or csv sheets

@WithinThePale - what would that look like? Designing the spreadsheet in Excel and then using Python to run scripts that function as queries?

If you have these selections noted somewhere, using excel to generate insert statements has proven speedy at work (e.g. https://stackoverflow.com/a/16819629).

Now THIS is an interesting idea!

If you're not married to the idea of a database, you could try Obsidian. It uses markdown, and you can add backlinks and hashtags that link things together. It also has a Git plugin, so you can keep everything synchronized. It also has a graph view that shows all the connections between everything. I use it to link my Bible notes to different topics.

If you wanna stick with a DB, I'd recommend PostgreSQL. Multiple GUI options, and it's FOSS. If you ever want to deploy it, it has great support whether you want to self host or use a cloud provider. It also has vector support for RAG if you want to implement AI into your workflow.

You might be overestimating my technical acumen - outside of the narrow area of IT in which I work, my proficiency at this kind of thing is, at best, average. LibreOffice Database (which is basically equivalent to MS Access) is probably at the limit of my ability at this point in time!

 

[WithinThePale]

@WithinThePale - what would that look like? Designing the spreadsheet in Excel and then using Python to run scripts that function as queries?

Input data in Excel, then move it around with code - don't use any formulas in the Excel. Mostly you just move it into Python dicts and use them as your basic datatype.

 

[Ploutos]

Input data in Excel, then move it around with code - don't use any formulas in the Excel. Mostly you just move it into Python dicts and use them as your basic datatype.

That sounds more complicated than using database software, at least for me.

 

[WithinThePale]

That sounds more complicated than using database software, at least for me.

Yeah, I've spent a fair slice of the last 4 years using it, so its the natural move for database stuff for me