Database design structure for storing courses chapters topics subtopics

Question

I'm putting together a database structure that can hold data for courses. Each course - will have several chapters and each chapter MAY have topics and each topic MAY have sub topics and so on.

This is sample data

Course - Accounts
1) Introduction to Accounts
   a) Basic Accounts
2) Financial Statements
   a) Income statement
      i) Depreciation
   b) Cash Flow Statement
3) Career as an accountant 

Now each of these topics will have lecture notes associated with them. Now the way I'm thinking is incase of Chapter 1 - "Introduction" - the notes are going to be associated with the topic "Basic Accounts". But in chapter 2 - "Income Statement" wont have any notes but "Depreciation" is going to have notes. Now since Chapter 3 "Career as Accountant" does not have any sub topics - the notes are going to be associated directly with it.

This is just my way or organizing - I can enforce rules saying each chapter must have a subtopic so notes can be associated etc.

This is what I have so far

Table: Course
course_id
course_name

Table: Topics
topic_id
parent_topic_id
topic_name
notes

Table: Course_Topics
course_id
topic_id

So the sample data would appear as

Course_id  |  Course Name
    1          Accounts

Topic_id  | Parent_topic_id  |   topic_name           | notes
    1             0             Introduction
    2             1             Basic Accounts
    3             0             Financial Statements
    4             3             Income Statements
    5             4             Depreciation

Course_id   |    Topic_id
    1               1
    1               2
    1               3
    1               4
    1               5

Here is what I'm looking for: 1) Is there a better way to design this - should I enforce stricter rules on how notes are associated with chapters or topics or sub topics

2) Secondly - given this data structure - if I wanted to pull all topics for a given course and display them in the chapter -> topic -> sub topic tree structure format. How would I do that. Should I just pull out all topics based on the course id and then in server side store them in data structures and organize them according to chapter/topic/subtopic wise or is there a cleaner way of doing it using sub queries.

Sorry for the long post - just wanted to clearly explain. Thanks for your time

****** EDIT ********

Thanks for all the answers guy. I came across this other approach of adding a lineage column and a deep column to the table.

The approach is explained here as "Flat table model done right" http://www.ferdychristant.com/blog/archive/DOMM-7QJPM7

What do you guys think of this approach.

Secondly - John raised a good point in his answer. How would I handle inserts in between.
- Chapters (parent_id =0) Say if I want to insert a new chapter between chapter 1 and 2
- Topic (parent_id != 0 ) If I want to insert a new topic between any two existing topics

Should I have another column that maintains the order like 1,2,3 and then if I want to insert something in between chapters 1 and 2 - insert it with a value 1.1 or something like that - can the same column be used for topics as well.

Thanks again

Answer 1

Skip the table course_topics if it's a one-to-many relationship.

You can make it

Table: Course
course_id
course_name

Table: Topics
topic_id
parent_topic_id
topic_name
notes
course_id

Notes seems fine if it's just one text field. If there are more you'll need to branch it out to another table.

Your 2nd question is very interesting... It can be done using a recursive join. ie you can use a CTE(Common table expression) to recursively join the table to itself. While displaying it just use a level field denoting which level in the hierarchy it is in and order it something like

order level
000   0
000.1 1
000.2 2
001.1 0
001.2 1

Just search for hierarchical data in sql... Since u use mysql and if you think u want to go for adjacency list or nested list here's an analysis. http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/ and also.... look at this question it explains a lot -> What are the options for storing hierarchical data in a relational database?

Answer 2

You can do all of this with just one table for Chapters and Topics. You need 2 fields, order which simply keeps track of the ordering of the items and level which is how many levels of indentation you need.

The benefit of this approach is that it removes the need for an arbitrary number of tables to capture chapter, topic, sub-topic, sub-sub-topic, etc.

The downside is you don't have any reference data around which topics belong to which other topics. But that information is computable. Also, you need to manage the order field whenever you modify a row.

Answer 3

It's not a bad first cut.

I'd add a section_designator field to your Topics table. If it's a chapter, you can call it "Chapter 3". If you have a section in that chapter, you can call it "Section 4" in that topic, and then concatenate the section_designators from that and all of its parents to get the full hierarchy ("Chapter 3 Section 4").

Nothing wrong with organizing the data server side as you've described.

Answer 4

Depending on your RDBMS (Relational Database Management Server), there may be a hierarchy_id data type, which is made for this exact kind of scenario.

The design that you're using is known as the "linked list" model or "adjacency list" model. There are definitely some big drawbacks to it, such as the requirement to use recursion for a lot of queries.

Another option is to use the "nested sets" model. A Google search for "joe celko nested sets" should yield some articles on the subject. You can also read his book on the entire subject of modeling trees and hierarchies in relational data models. This method also has some drawbacks (updates, inserts, and deletes can be more complex).

I would check out all three models and decide which fits best for your situation.