SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lesson Plan structure...a bit complex

    OK, I;m developing a large database structure in which part of it includes lesson plans instructors prepare for there classes on a regular basis. Fot now let's say the classes are Literature. Now this would usually be striaght forward, but the structure must accomodate 3 different templates for the layout of lesson plans with varying degrees of complexity.

    The templates are:

    Template A (Low Complexity):
    Code:
    Just one large textfield for each day to write the goals of the day.
    
    EX.
    June 29, 2005
    _______________
    Go over symbolism in Shakespeare's comedys with emphasis on "As You Like It" and "The Comedy of Errors"
    Template B (Medium Complexity):
    Code:
    Similiar to Template A.
    This template breaks the class period up into time segments (beginning of class, middle, and end)
    and each segment has a textfield to fill in what should be accomplished.

    Template C (High Complexity):
    Code:
    Up to 7 rows of 3 textfields each which describe topic, author, books
    
    EX.
    June 29,2005
    ____________
    Topic: Communism
    Author: Ayn Rand
    Books: "Atlas Strugged", "We the Living"
    
    Topic: Combat
    Author: SunTzu
    Books: "The Art of War"
    . 
    .
    .
    So when an instructor adds a new plan, they chose a template and complete it accordingly. Instructors are free to use any template for any day.

    I started the schema like so:

    Code:
    INSTRUCTOR
    ______________
    ID
    name
    
    
    TEMPLATE
    ___________________
    ID
    Description
    
    
    LESSON_PLAN
    _________________
    ID
    Date
    Instructor_ID
    Template_ID
    ?
    ?
    ?
    Any thoughts?

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Depends on how much you want to adhere to normalised database design, and how rigid you want the structure to be. Quickly scanning the problem, I count at least 6 tables:

    BOOKS (ID, Author, Title, Description etc)
    LESSONS (ID, INSTRUCTOR_ID, StartTime, EndTime)
    LESSONS_BOOKS (BOOK_ID, LESSON_ID)

    TOPICS (ID, TITLE)
    BOOK_TOPICS (BOOK_ID, TITLE_ID)

    INSRUCTORS (ID, NAME)

    This allows you to associate books with a lesson, but not in any order. Do you need to present the books in order? Also, this schema requires that if teachers wish to present a new book, they must first add it to the database (Although this can be made seamless with good UI design).

    Hope this helps,

    Neil.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it depends on how tightly you want to shoe-horn your users in to using a particular model. if you don't mind them beign able to violate convention, then you can just make the notes one TEXT field and present them with a choice of three text templates.

    otherwise, i would do this with three tables.

    the first table (which could just be the class table) has two fields: plan_id and plan_type where plan_type is a, b, or c.

    the second table holds types a and b. one column whould be id, and the next would be segment_id. with type a, segment_id would be null because you only have one segment. with type b, segment_id would be 1, 2 or 3 to represent the beginning, middle or end. use a TEXT column to represent the data.

    the third table holds type c. you would need an id column, plus columns for the book, description and author.

  4. #4
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both for the responses.
    Longneck, your 2-table idea sems to be a good start. Also keep in mind that the field names are somewhat fluid. What may be books in one disciplne may be computer programs in another. So before I get any deeper, I'd like to step back and ask a more general question.
    Suppose I had 3 types of ways to format lsimiliar data.
    Option 1 uses data types A, B and C.
    Option 2 uses data types D, E and F.
    Option 3 uses data types G, H and I.

    Can I have a table called PLANS that has the following columns:
    ID
    Instructor ID
    Option ID
    TypeA
    TypeB
    TypeC
    TypeD
    TypeE
    TypeF
    TypeG
    TypeH
    TypeI

    So if a lesson plan is entered using Template Option 2. The cols for types D, E, and F will be filled, the others will be NULL.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    sure. that would be acceptable.

    but, based on your original question for template C, this is NOT acceptable:
    Code:
    ID
    topic1
    author1
    books1
    topic2
    author2
    books2
    topic3
    author3
    books3
    topic4
    author4
    books4
    topic5
    author5
    books5
    topic6
    author6
    books6
    topic7
    author7
    books7

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there,

    I'll be honest, I'm a bit confused as to what you're trying to acheive. Could you define what you mean when you say 'Plan' and 'template' in the context of a lesson?

    Kind Regards,

    Neil.

  7. #7
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll be honest, I'm a bit confused as to what you're trying to acheive. Could you define what you mean when you say 'Plan' and 'template' in the context of a lesson?
    No problem, Neil. MY head is still spinning from when the client tried to explain this to me.


    OK

    A "Lesson Plan" is just a group of info that defines what the class will do one a certain day. Now a "Template" is how that information is structured.

    Is the Lesson Plan just a blob of text in one textfield?
    Is the Lesson Plan broken down into time periods?
    Is the Lesson Plan organized by task?

    These are questions that depend on the organization level of the instructor.

    Make more sense, now?

  8. #8
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    but, based on your original question for template C, this is NOT acceptable:
    Code:
    ID
    topic1
    author1
    books1
    topic2
    author2
    books2
    topic3
    author3
    books3
    topic4
    author4
    books4
    topic5
    author5
    books5
    topic6
    author6
    books6
    topic7
    author7
    books7
    Oh yes, obviously not.

    I guess what's getting me is that I was trying to find a way to abstract the data from the templates. So only raw data is stored and arranged according to its Template ID. But the info seems to be a bit to dissimiliar. In other words, I might not able to take the exact same lesson plan and structure it in ALL three templates.

  9. #9
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I came up with a possible structure. Seems a bit bulky, what do you think?
    Attached Images Attached Images
    Last edited by tconley79; Jul 28, 2005 at 18:20.

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    do format 1, 2 and 3 correspond to the formats in your first post?

  11. #11
    SitePoint Zealot tconley79's Avatar
    Join Date
    Feb 2004
    Location
    Miami
    Posts
    176
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do format 1, 2 and 3 correspond to the formats in your first post?
    Well I'm going more general so , no, this is just a study on how to format a database with this situation. Which then applied to a real example.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •