I previously created an financial forecasting DB that is working very well but I don't like the way in which I designed it.
If I can automatically create multiple portal records based on a year range and a calculation for total amount, it allow the DB to contain hundreds of records instead of hundreds of fields (which it currently contains).
Here's an example of the approach:
Parent Table = Expenses
Fields: Description, Start Date, End Date, Frequency (weekly, monthly, yearly...)
Child Table = YearlyExpenses
Fields: Year, TotalExpense
The Expense layout will have numerous records - each being an expense item.
A portal will exist based on the child table records.
Child records are automatically created for each year that the parent expense item spans. For example, an expense - "Groceries" starts at today's date and runs to March 31, 2038.
Here's what I want to happen via script (maybe a button click or exit of a parent field).
- Create a new portal record who's child field - "Year" is set by a calculation to "2018".
- Perform a calculation that sets the child field - "TotalExpense" based on a predefined calculation.
- Repeat steps 1 & 2 creating a new child record up to and including Year 2038 (21 child records total).
Note that the TotalExpense calculation needs to look at the value of the Year field as the calculation can have 3 possibilities:
Case(Year = current year, do calc A;
Year >current year and < final year, do calc B;
Year = final year, do calc C)
Currently the DB does not use a portal. Instead, the expense table contains fields that define each expense record (description, start date, end date...) and Year fields to cover each year over a range of 75 years. A TotalExpense field exists for Year field and it's value is based on a calculation. There are also Summary fields for each year that sums all expenses for a given year. This requires 150 fields! Multiply that with other tables such as Income, Investments, Loans, Mortgages....and there's a huge number of fields! Surprisingly the current DB is still very quick.
I'd rather have fewer fields and more records by using portal records for the Years and yearly expense total for a given expense item.
Is the new approach a better design that the current one?
Can I automatically create multiple portal records as described above?