Reputation: 93
I'm working on a database that will hold Books > Chapters > Sections > Paragraphs. The structure is such that a book may not have chapters, but it will still have paragraphs, and a chapter may have paragraphs without sections. The objective is to load all the chapters, sections, and paragraphs of a selected book when the user chooses a book.
Here's an example of the structure:
* Book
** Chapter
*** Section
*** Paragraph
I'm currently stuck on how to structure this database efficiently, especially when a chapter may not have any sections, and a section may not have any paragraphs. I'm considering two options:
Using a tree structure where each node in the tree represents a book, chapter, section, or paragraph. I'm thinking of using a self-referential foreign key to represent the parent-child relationship between the nodes. For example, a book may have many chapters, and each chapter belongs to a book. Similarly, a chapter may have many sections, and each section belongs to a chapter.
Using a denormalized structure where each record holds all the information about the book, chapter, section, and paragraph. I'm thinking of creating a separate table for each level (i.e., books, chapters, sections, paragraphs) and linking them with foreign keys. For example, a paragraph would have foreign keys to its associated section, chapter, and book.
However, I'm not sure which approach would be better, especially when it comes to querying the database to load all the data for a selected book efficiently.
Keep in mind that you have to create an admin panel, where you have to navigate through book > chapter (you may see empty records for paragraphs without chapters) > sections (same as chapters) > paragraphs.
Any advice or suggestions on how to structure this database would be greatly appreciated!
Upvotes: 0
Views: 54