G
Guest
Hi all
I'm having an extremely difficult time trying to design a database for a
Childcare centre. There seems to be a lot of variables, but the basic
structure is probably a General Ledger type system or Receipting system.
I've never had to programme such a database before and I cannot find an
example database that is similar to what I need.
The basis of the system is as follows: Statements are to be produced on
monthly basis with the amounts owing itemised weekly. Receipts are entered
based on the week received. Other variables include a Tier system of Daily
Rates, Discounts for Full Term paid, changes to weekly care days (ie. a child
may be off for a day and therefore not have to pay that day), Security
Deposit that can be returned on unenrolment of child, etc. Multiple children
also need to be shown as a total on the Statement. Balances need to be
brought forward for each month/term. School vacations/holidays are not
payable.
So far I have the following:
tbl_Parents (Parents info, PK Parent_ID)
tbl_Children (Children info, PK Child_ID, FK Parent_ID)
tbl_TermDates (Stores dates of the school terms, PK Term_ID, Start_Date,
End_Date)
tbl_Rates (stores the different rates, PK Rate_ID, FK Child_ID)
tbl_Receipts (stores the receipts based on week no., PK REC_ID, FK Wk_No)
tbl_TermWkNos (generated via code to calculate the week numbers in each
term, PK Wk_No, FK Term_ID)
I'm having a lot of trouble with working with the Terms, and no doubt with
future years. I'm not quite sure how to work with multiple years' worth of
data when the data is classified by term dates. I find I'm having to work
with FULL OUTER JOINS, which don't really translate well in JETSQL, and I
have read in other posts that this is not good design anyway, is this
correct?!?
I'm very confused by all this and would appreciate any help possible.
Let me know if you need any more information.
I'm having an extremely difficult time trying to design a database for a
Childcare centre. There seems to be a lot of variables, but the basic
structure is probably a General Ledger type system or Receipting system.
I've never had to programme such a database before and I cannot find an
example database that is similar to what I need.
The basis of the system is as follows: Statements are to be produced on
monthly basis with the amounts owing itemised weekly. Receipts are entered
based on the week received. Other variables include a Tier system of Daily
Rates, Discounts for Full Term paid, changes to weekly care days (ie. a child
may be off for a day and therefore not have to pay that day), Security
Deposit that can be returned on unenrolment of child, etc. Multiple children
also need to be shown as a total on the Statement. Balances need to be
brought forward for each month/term. School vacations/holidays are not
payable.
So far I have the following:
tbl_Parents (Parents info, PK Parent_ID)
tbl_Children (Children info, PK Child_ID, FK Parent_ID)
tbl_TermDates (Stores dates of the school terms, PK Term_ID, Start_Date,
End_Date)
tbl_Rates (stores the different rates, PK Rate_ID, FK Child_ID)
tbl_Receipts (stores the receipts based on week no., PK REC_ID, FK Wk_No)
tbl_TermWkNos (generated via code to calculate the week numbers in each
term, PK Wk_No, FK Term_ID)
I'm having a lot of trouble with working with the Terms, and no doubt with
future years. I'm not quite sure how to work with multiple years' worth of
data when the data is classified by term dates. I find I'm having to work
with FULL OUTER JOINS, which don't really translate well in JETSQL, and I
have read in other posts that this is not good design anyway, is this
correct?!?
I'm very confused by all this and would appreciate any help possible.
Let me know if you need any more information.