My WordPress system contains posts with a field containing Bible references and ranges, eg.
John 5,1-10; Matthew 3,2; Mark 1,1-5
Users searching for a Bible references e.g. John 5,3
or Matthew 3,2
or Mark 1,2
would all need to find the above post.
From an SQL point of view is this even possible? If not what would I have to change in this approach?
Best Answer
This is very doable, assuming you setup the structure correctly.
This
post
table is a placeholder to make the queries work. This table already exists in your project, under some name (I don't know what), and holds your posts that have references to Bible verses.Next up, we have a table to define all the books of the bible. It is not strictly necessary to have a separate table, and purists will lash me for not making use of natural keys, but I choose to include this table to minimize the size of the indexes in other tables. This only needs to be populated once, with all the books of the Bible.
The
bible_verse
table also only needs to be populated once, and would contain every verse in the Bible. Actually, if you only need to reference the verses and not return the text of the verses you can completely skip this table.If you choose to include this table, you may choose to add a FOREIGN KEY constraint from
post_bible_verse
to reference thisbible_verse
table.The
post_bible_verse
table is the work-horse of this setup. Here, you recover each individual verse that is referenced in a post. You'll have to translate ranges into a list of individual verses using some server side code, but once done you can easily insert into here each time a post is made referencing some verses.To see how this setup works, lets populate some values.
First, the
bible_book
table. I used the "number" of the book in the Bible as thebible_book_id
primary key. That is, John is the 43rd book of the bible, so it getsbible_book_id
of 43. Etc.(Remember, this only has to be done once in your case, to include all books.)
Then, we populate all the Bible verses. As mentioned before, this is not necessary if you don't need to return the actual text of the verses.
This INSERT statement includes all the verses you mention in your example.
Create a post to represent what is in your system. (You won't need to do anything in your real setup, as your blog posts already exist and presumably have some
id
column to use.)And finally, insert all the verses that correspond to this post. You'll have to include code to insert these verses when a post is made or updated, as a text range will not work.
Now lets see it in action.
User searches for John 5,3. Server parses that and runs the following query:
And it returns
post_id
of 1 (which is the post that referenced those verses.)User searches for Matthew 3,2:
Again found our post.
Now Mark 1,2.
And finally, search for Matthew 3,1, which is NOT referenced by your post.
And we verify that the post is NOT found this time.
So, yes, it is very possible to create a database structure that allows you do search for posts based on Bible verses, but it does require an appropriate setup, and parsing of the ranges into specific queries.