Results 1 to 8 of 8

Thread: design problem

  1. #1
    Join Date
    Jul 2009
    Posts
    168

    design problem

    Hi,

    I currently have a number of tables in my student database and I am just worried about some aspects of the design. It's about later changes that may be made to the tables. I have tables like:

    Courses
    -------
    - courseID (PK)
    - name
    - fee

    StudentFinance
    ---------------
    - financeID (PK)
    - studentID (FK)
    - courseID (FK)

    This is just part of the tables' definition as I want to focus on one aspect. Suppose, the fee changes over time, the data would become corrupt. What is normally done to prevent this?

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    The data doesn't have to be "corrupt" just because the fee changes. The data may become out of date, but keeping the data up to date is not a database design problem, it's a question of management and process.

    If you want to record the history of values as they change over time then you need a "temporal" or "history preserving" model of your data. In other words your tables must preserve each change, usually as a new row for each new value.

  3. #3
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    The easiest way to keep history for this situation is add a Semester table (with attributes of year, semester # within year). Remove the fee from the Courses table and create a CourseSemester table (with CourseID, SemesterID, and fee). Then link the StudentFinance table to the CourseSemester table instead of the Courses table. That way you keep the fees for each semester and keep a record of what each student paid for each course they took (with the added bonus of when they took it).

    The most efficient way to keep history for this situation is to add ValidFrom and ValidTo dates to the Courses table then add a CourseTaken date to the StudentFinance table. There is a decent article on temporal database design on Wikipedia.org to help understand how this would work.
    Last edited by MarkATrombley; 11-17-09 at 09:38.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    when's the final project due?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    Thnks for your replies. Very informative and concise. These are my other tables to help keep track of history of things.

    TokenFees
    ----------
    - tokenFeesID (PK)
    - courseID (FK)
    - tokenID (FK)
    - fees
    - academicYear
    - date
    - semester

    AcademicsToken
    ----------------
    - tokenID (PK)
    - name
    e.g of tokens would be: exams, dissertations, course

    OwedFees
    ----------
    - owedFeesID (PK)
    - tokenFeesID (FK)
    - date
    - studentID (FK)

    StudentFinance
    ---------------
    - financeID (PK)
    - financeEntityID (FK)
    - studentID (FK)
    - entityID (FK)

    @dportas: So to the TokensFees table i only allow 'modify' the actual row if there are mistakes or errors but updates create a new row with a date.

    @mark: The ValidFrom and ValidTo is interesting, I can integrate that with the Course table but I guess the semester thing is taken care in my tables isnt it?

    @brett: isnt a final project or assignment my friend as many people think it is, or maybe it is because I want to finish this software (final project in this case) ASAP for a business school that needs to keep track of its students' accounts. They don't want a complicated thing because they are just business people like you know they want simple things KIS
    Last edited by kpeeroo; 11-17-09 at 22:20.

  6. #6
    Join Date
    Jul 2009
    Posts
    168
    Okay one more thing to history. What if the name of the course changes over time with slight modifications in its name? Then we wouldnt know the exact name of the course the student took at that time. Or is it necessary to do so? like i think dportas suggested once in a thread it's YAGNI thinking. But am just curious and want the program to be as informative as possible.

    So in this case, will it be like dportas suggested to add a new row each time and also like mark said to add validFrom and validTo dates to the Courses table? I think that is the best combination dont you think? in this way everything stays on record over time.

  7. #7
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    I am not sure that I like OwedFees. I would call it StudentFees and add a paid amount (which could represent a full or partial payment) or a paid date.

    Yes, since you have semester in the TokenFees table that takes care of at least part of your history. If you want absolute history then you add the ValidFrom and ValidTo dates to everything. This can be a difficult concept to handle for many, so I would get your basic design working first, then add the valid dates. (As always, my opinion )

  8. #8
    Join Date
    Jul 2009
    Posts
    168
    thanks for your advice and opinion mark. appreciate it. I have another table to handle the full or partial payment thing although I like the change in name from OwedFees to StudentFees very much! always a problem to choose a suitable name.

    Payment
    --------
    - paymentID (PK)
    - amountPaid
    - owedFeesID (FK)
    - date

    You did reply to one of my threads on this financial aspect of the project and was successfully adopted

Posting Permissions

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