Re: representing Table of Contents in a DB

Hi Oliver,

Based on your description of the problem, I think there are a number of possibilites that are available to you. However, I'd want more info to be sure. I'd firstly want to examine the queries that you might perform on you database. Some possible examples might be:

• Select all questions that require knowledge of chapter 2 in the coursebook
• Select all questions that requiest knowledge of chapter 1 section 2.a.3.c
• Select all chapters that are not referenced at all by an exam.

Do you have any more examples? These might help you test your design before commiting to it. Next, I'd want to clear up a few assumptions also. Here's some I've drawn from your problem description, which I actually imagine are incorrect?

• The system should allow multiple exams to be stored?
• Questions can only have one reference to the course book?
• There is only one course book?
• Books are the only thing that a question may have a referenced to, not papers, journals or videos.

The reason I ask about these is that your problem with making references may be amplified if there are many resource types. One possible structure is below, and involves sub-typing.

Exams
- examId (PK)

• examName
• etc...

Questions
- examId (PK)

• qustionId (PK) -- part of composite PK
• etc...

References
- referenceId (PK)

• referenceTitle
• etc..

Questions-References (n:m join table)
- questionId

• referenceId

The next step would be to create tables for storing different types of references. Each woud be sub-type of the References table.

StrictBookReferences
- referenceId

• chapter
• subsection1
• subsection2
• subsection3
• etc

LooseBookReferences
- referenceId

• chapter
• pageNumber

VideoReferences
- referenceId

• timeoffset

This approach is flexible, but more requires work than others might. Other options could include storing your references as free text, but with check constraints on them (must have 'chap' in text etc) My main thought is whether you need to store the references in their atomic form? Hopefully some example queries would demonstate how your system needs to use the references (or more importantly - how it doesn't need to use them!).

Hope this helps!

Tobin

"oliver abroad" <oraustin_at_hotmail.com> wrote in message news:da9f974.0209220924.70c71ff8_at_posting.google.com...

> I'm writing a db that holds examination questions.  Each question has
> a reference to a place in the coursebook. The coursebook is divided
> into chapters, sections sub sections etc so a reference looks like
> chap 1 section 2.a.3.c for example (5levels of specialisation in this
> case) Each level has a title. eg. Chap=Radar then subsection=Parabolic
> sub-subsection =vertical beam.
>
> ok that's clear I hope.
> How can I store the table of contents for this book in such a way that
> I can use in the db and reference questions.  I mean one approach is
> to have one table with an entry for every single reference possible.
> The fields would then be the chapter, sub section, sub sub section etc
> for this ref. But this means a lot of repeated data.
>
> I can't think of a better way to represent it and I'm guessing there
> is a more elegant solution.
>
> So please have a think and let me know :o) thank you very much Oliver