Database schema for interlinked texts

I have the task of designing a bible database, non-affiliated to any group ;)

The problem is like this I have 1 bible verse, 18 different commentaries on the bible verse, 5 commentaries on the commentary. The plan is to make a WPF application that will be available so that users will have the text marked with the different commentator’s comments on the side of the screen.

What I would like done is the following, When a person looks at the verse, the commentary will be highlighted on a button on the side of the screen , when he looks at the commentary the verse will be highlighted, also I would like to make other verse quotes highlighted and searchable.

I will need to precompile everything into sql and then export it into SQLite. I have broken the data into the following form.

BOOK
CHAPTER
PARAGRAPH
VERSE
CommentatorName
CommentatorComment
VerseINComment location

So I have the following problems

1: Data is entered in a stagnated form (A verse will be entered multiple times, but only have one comment from the same author) We will have a block of data in the database. I will need to process the data looking in the comments for Verse quotes. How should this be done? Should all the DATA be entered into the database then a program run to search the database, or can I somehow use tsql to search the commentators comments for verses and then mark them?

2开发者_Python百科: What is the best way to store the location of VerseINComment location? Meaning I was thinking some form of mapping, like they use in real maps , 1,1,1,1 would indicate GENISIS,CHAPTER 1 Paragraph 1 VERSE 1 (ACTUAL = In the beginning the ……). Any thoughts on this would be greatly appreciated.

ETT (ehost Think Tank)

It's best to prevent bad data from entering the database, rather than removing it after the fact.

As the data is entered, you should check for possible duplicates in the existing data and prompt the user when this happens.

I'd probably go with something like this as an underlying data structure.

-- (UI1) = UniqueIndex1

Book (Id (PK), Name)
Verse (Id (PK), BookId (FK / UI1), ChapterNo (UI1), ParagraphNo (UI1), 
        VerseNo (UI1), VerseText)
Commentator (Id (PK), Name)
Comment (Id (PK), VerseId (FK), CommentatorId (FK), CommentText)
CommentOnComment (Id (PK), CommentorId (FK), CommentId (FK), CommentText)

It doesn't make a lot of sense to split Chapter/Paragraph/Verse out to their own tables, since each will just be an integer value. Commentators would be able to comment on a verse that had already been entered without the need to re-enter the text themselves.

Having comments on comments hints at some sort of on-going dialog, so you'd probably want to add some way of identifying the order in which these were made (e.g. record creation date / sequence no.). I've assumed that you're not looking for "comments on the comments on comments", which is tree-like and requires a self-referential table.

If you want to provide a mechanism for easily referencing the verse (1,1,1,1 as suggested) this should be mapped at data-entry to the appropriate Id from the Verse table. You would have to be mindful of the Id assigned to each Book, in this case.

Cross-Edition Comparisons?

If your app will allow commenting on / comparison of different Editions, you could put Edition as an table alongside Book:

Edition (Id (PK), Name)
Book (Id (PK), Name)
Verse (Id (PK), EditionId (FK / UI1), BookId (FK / UI1), ChapterNo (UI1),  
        ParagraphNo (UI1), VerseNo (UI1), VerseText)

I'm no expert when it comes to the differences between editions of the bible, but you would be able to readily identify the same passage in different editions by filtering on BookId, CapterNo, ParagraphNo, VerseNo, if that is your wont.

Hope this helps, Brett.