I have not worked on my LDS Scriptures project in a very long time. In fact, annual realeases are becoming an embarrassing reality. However, I’ve been itching to update the project for a while now, since I’ve been learning quite a lot more about databases since my last release.
I had an interesting idea for doing some custom RSS feeds of pulling down scriptures (which I’ll get into at a later time), but I quickly realized the current db schema wasn’t quite up to par. I started poking around the postgres one, and it was much easier than I imagined to fix it up quite a bit. I dropped all the tables and recreated them, this time with primary and foreign keys, and I got rid of some columns that were completely unnecessary. I renamed some too, along with the other general cleanup.
I tarballed the snapshot once I was finished. This isn’t what I’m going to release as the final 3.0 version, since I still need to add things like views and more indexes, but this is the direction I’m going to be heading. You can download the snapshot tarball here. Feedback is welcome, as always. 🙂
Speaking as a programmer, those table names are hideous. I’d go nuts writing a program that accessed those tables and even some of the fields:
lds_scriptures_verses.verse_scripture
would be a pain in any language. Even if I wrote a wrapper for it (which I would) I would still have to keep in mind what I had aliased to what.
Might I suggest the following?
lds_scriptures_volumes => volumes
lds_scriptures_books => books
lds_scriptures_verses => verses
lds_scriptures_verses.verse_scripture => verses.vtext
(I’d rather just do text, but that’s a reserved word.)
Also, the following view in its various forms (long title lookup, short title lookup, lds_org lookup, etc.):
CREATE VIEW verse_lookup AS
SELECT vol.title, book.title, v.chapter, v.verse, v.verse_scripture
FROM lds_scriptures_volumes vol, lds_scriptures_books book, lds_scriptures_verses v
WHERE v.book = book.id
AND book.volume = vol.id