Self-join or simpler

[jim68]

I have what should be a simple scenario, listing book references (Book, chapter, verse - e.g. for the Bible you would have Genesis 6:1; 6:2, 6:3; 7:1; 7:2 etc...).
In theory Book is a 1-many relationship to Chapter and so on, but it would seem too much to make three separate tables. So I thought a self-join would be better.

Presently I have a table tblReference containing:

RefID
Book
Chapter
Verse

Do you think a self-join is best, joining, verse to chapter, and chapter to book? And, if so, where would the joins be (I have been reading on the web about self-joins but I a little confused). Do I join Book to Chapter, or do i need to create a ChapterID, BookID and VerseID also?

Thanks for any advice.

 

[MichaelRed]

as long as the number of entities to join is limited/fixed, a self join can be made to work to list the dependent subset from a given starting point. See thread181-641018 for some additional info/suggestions. In general, I have found many are not all that comfortable using the 'self-join', so would reccomend the multiple table approach as being somewhat more in line with current recommendations re structure, but there is no reason the self join cannot be implemented.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jim68]

Thanks.

Certainly it is easier to manage if I have spearate tables.