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.
Anyway, if you want to download this test schema, its available here. As always, feedback is welcome.
Looks good.
For the indexes, you might want to add indexes for the lds_org columns in each of the tables as well.
chapters looks like a lookup table … if so, I’d say just have one index on both columns, and you don’t have a fk restraint on the chapter column.
In verses, I would have one index on the chapter and verse columns. You’re not likely to ever want these separately, I don’t think.
Finally, I’d suggest not trying to be so complex with your view. Only sellect the volume_id, book_id, chapter_id and verse_id. Otherwise, as a programmer I’d be throwing away a lot of data most of the time, since I would most likely want to build the information based on user criteria.
Oh, yeah; the ORDER BY in your view only works because you’ve forced the data to fit (verse #1 is Gen 1:1, #2 is Gen 1:2, etc.) … it would be better to do ORDER BY b.volume_id, c.book, c.id, v.id
Keep it up … you’ll have me studying the scriptures again in no time! :>