Well, that was fast. I looked at the schema last night for the MDP Scriptures project, and started cleaning it up, and it went really quickly. I’ve got a postgres dump all ready for review, and this is probably the configuration I’ll use for the next release.
The major change was that I added a new table for the chapters. It seems a little odd having the chapter number in a table all its own, but for a normalized database schema it makes perfect sense. The only thing I don’t like is now you have to INNER JOIN across four tables just to get all the information. Most of the time you won’t need anything but book + chapter + verse, which is only three tables. I did create a sample view called view_verses which pulls them all together so you can easily run a select on some format like ‘Gen 1:1’. The thing I don’t like is that even that view is CPU intensive, so I may have to look at changing some stuff around.
Aside from that basic view, I’ve decided I’m not going to put all my fun ideas for functions and views in the packaged release. Instead, I’ll just have them either as a separate release, or just post them on the website since I’m sure they will evolve.
One really cool thing about postgres that I love is that you can have overloaded functions. I started playing with them a while back on this database, and came up with some cool concepts. One idea I want to implement is being able to run a select statment using a between on two verses. An example query would be: “SELECT * FROM view_verses WHERE verse_id BETWEEN verse(‘Gen.’, 1, 5) AND verse(‘Genesis’, 12); where the verse() function would be overloaded to take between one and three arguments: book, chapter and verse.
It’s pretty cool all the stuff you can do with postgres, and that’s definately where I’ll be focusing my attention in getting the goodies done.