What's the best way to store the bible in SQL?
What I'm looking for is a breakdown of table names w/ corresponding fields/types.
The bible I want to store will be in English and needs to support the following:
- Section Titles (can show up within verses and in-between verses)
- Smallcaps Text
- Red Letter Text
- Verse Numbers
- Footnotes (can show up within verses and within section titles) (may optionally reference another verse)
- Cross-references (essentially a footnote that only references another verse and doesn't add any commentary)
- Anything else I'm forgetting
Rather than reinventing the wheel, you might consider using a "Bible SDK" such as AV Bible, which stores text, formatting, verse numbers, etc. in an open, custom binary format.
I think they have everything you've listed except cross-references.
Here is another collection / example for you:
Here you will see SQL, XML, CSV, and JSON. Of special note is the cross-reference table (quite extensive and amazing) and a simple verse-id system for fast queries.
EDIT: Notice the ids of the tables are book-chapter-verse combinations, always unique.
SQL is th BEST way to store this. Considering your requirement we can split them into two major parts
Information that's dependent on individual version
- Small caps
- Red letter print
Information that isn't dependent on individual version
- Book, Chapter, Verse numbers
- Section title
- Foot notes (??????)
- Cross Reference
For various reasons I prefer to store the whole bible project into one SINGLE table, Yes call it as bible
For your visual here is my screen I have stored nearly 15 versions of bible in single table. Luckily the different version names are just kept as column wide. So when you add more version in future your table grows horizontally which is okay thus the # of rows remain constant(31102). Also I will request you to realise the convenience of keeping the combination of ('Book, Chapter, Verse') as the PRIMARY key because in most situations that's the look-up way.
That said here is my recommended table structure.
CREATE TABLE IF NOT EXISTS `bible` ( `id` int(11) NOT NULL AUTO_INCREMENT, --Global unique number or verse `book` varchar(25) NOT NULL, --Book, chapter, verse is the combined primary key `chapter` int(11) NOT NULL, `verse` int(11) NOT NULL, `section_title` varchar(250) NOT NULL, -- Section title, A section starts from this verse and spans across following verses until it finds a non-empty next section_title `foot_note` varchar(1000) NOT NULL, -- Store foot notes here `cross_reference` int(11) NOT NULL, -- Integer/Array of integers, Just store `id`s of related verses `commentary` text NOT NULL, -- Commentary, Keep adding more columns based on commentaries by difference authors `AMP` text NOT NULL, -- Keep, keep, keep adding columns and good luck with future expansion `ASV` text NOT NULL, `BENG` text NOT NULL, `CEV` text NOT NULL, PRIMARY KEY (`book`,`chapter`,`verse`), KEY `id` (`id`) )
Oh, What about the Small caps and Red letters?
Well, Small caps & Red letters you can store in version columns using HTML or appropriate formats. In the interface you can strip them off based on user's choice whether he requires red letter or small caps.
For reference you can download the SQLs from below and customise in your way
I also found http://www.lyricue.org/downloads/ that includes several bible translations in mysql format.
Everything WernerCD's answer, but store the verseText as xml so you can add formatting tags like <red>e.g. Red Text</red> and use the tags to format it in your application
Mark Rushakoff's answer is probably the best for you specific need. However more generally if need to store content that either has data within the content or if you need to store data about the content a Content Management System is typically used. You can build your own (which WernerCD's answer had a table structure for) or use a CMS product. The list here shows the wide variety of technologies used (around 30 in this list use MySQL)
This repository contains entire Bible given in sql.