Creating a Bible Database

[Asda]

Dear All,

FMPA 9, Win XP sp3.

I am just beginning a Bible database and was wondering if I am on the right track here.

I was wondering if this requires the use of Recursive Relationships or can be done using our good ol regular relationships.

Basically I have 3 tables- Books,Chapters, Verses.

Minimum structure is key field in each table -say BookID in Books, chapter ID in chapters etc.

Relate BookID to chapters with a foreign key field called BookChapterID.

Book 1 will have say 1-5 chapters.

Book 2 will also have 1-4 chapters etc(all different of course)

It is the multiple instances of chapterID 1 in the chapter table that is worrying me. Even though each occurane of chapterID 1 will have a unique BookID associated with it.

So Am I on the right track here.

Thanks for deciphering the above and replying.

[comment]

I had a slight problem problem with the deciphering, so let me state it in my own terms:

• A book has a unique BookID (primary key).

• A chapter has a unique ChapterID (primary key) and also a BookID (foreign key - holds the value of the parent book BookID).

• A verse has a unique VerseID (primary key) and a ChapterID (foreign key).

The relationships are:

Books -< Chapters:

Books::BookID = Chapters::BookID

Chapters -< Verses:

Chapters::ChapterID = Verses::ChapterID

You don't need a recursive structure, at least not until you get to cross-references.

[Asda]

Thanks comment.

Your deciphering was perfect

Yes, that is what I have. So I amon the right track here.

I refined my Primary key in the chapter ID- I concatenated the BookID and ChapterID and used that new filed (chapter_Key)as primary key from chapters to verses.

Books -< Chapters:

Books::BookID = Chapters::BookID

Chapters -< Verses:

Chapters::Chapter_Key = Verses::Verse_Key

Working fine so far..but then I just got started.

Any thing i should be wary of , in your vast experience.

thanks again.

kev

[comment]

I don't see the need for this refinement. All a verse needs to know is which chapter it belongs to. The book is determined by the chapter.

Note that ChapterID is not the same as chapter number: chapter numbers restart from 1 for each book, while ChapterID is a unique identifier - it runs consecutively through all the chapters, from 1 to 1189(?).