locked
Revenue Cost Design RRS feed

  • Question

  • Hi,

    I have a good knowledge of Dimensional Model and have implemented many. However I'm clueless for the below as it needs slightly a flexible model. Any guidance (of links/ materials) on how to design a DB for the below requirement would be helpful. A small model example may also be very much helpful.

    The objective of this model is to capture and DISTRIBUTE cost/expense and revenue. And report on it.

    There are different levels.

    College/University (1-highest)
    department
    professors
    Students (4 -lowest)

    • Each student pays a tution fee for a period
    • Many students roll up to a professor, each student may have more than one professor (many to many)
    • Professor roll up to a department, may also have multiple department.
    • Department rolls up to university.

    Now lets say, a charity ABC provides a check of $100,000. After administrative cost of 15%, the remaining amount has to be spread across department based on the # of students and professors for that department. Amount allocation is not to be fully automated as Finance needs some control on how much to allocate for a department and capability to move some percentage to other.

    Sometimes fund comes in at a department level.

    $50,000 for department A
    $20,000 for professor.

    Again, fund is not evenly spread across student (need some control), however based on the ratings of the student, the top 50 student receives 70% of fund, then the rest.

    Its not only top down approach, its bottom up approach as students also pay money which rolls up professor, department, and univ  (revenue) by month, quarter, year

    How do I model to track this? fund allocation, contribution, expense, revenue. I guess if it goes through the lowest level it would be great.

    Thursday, October 11, 2012 2:24 PM

Answers

  • I think you started on the right track - but the complexity can be (and likely will become more) complex.  A fund (perhaps "budget" is a better term?) is [choose preferred verb here] by an [another preferred noun that represents ownership/responsibility/benefit] and that entity is one of (college, department, ...).

    To put a bit more flavor to that, let's say that a fund is allocated to a fund manager who is either a college, department, professor, or student.  The fund manager is a supertype to the current set of 4 subtypes (college, ...).  The fund itself will be associated with transactions (leaving this bit up to you) that increase or decrease the balance.  This can quickly get complicated since there can be an important time element involved (grants do not last forever, funds must be spent within specific timeframes, fiscal years are often involved, etc.).   The tracking of gifts, donations, grants is equally daunting - and keep in mind that you might need a place to track unallocated amounts of these transactions since it is often possible to allocate only part of the total amount.  Note that this is a more generic accounting approach (and more flexible at the cost of complexity).  The alternative is to focus on the incoming amounts as entities themselves which are then allocated to [replacement noun similar to fund manager] who is either a college, department, ... 

    I will also note that you may have overlooked another important relationship.  The relationship between professor and student may not be direct.  Typically there is at least one other entity - class or course - which is taught by the professor and taken by the student.  And, typically again, students pay fees for the courses they take. 

    Friday, October 12, 2012 3:24 PM