Sql-server – Database Design for Forecasting Review

best practicesdatabase-designsql server

I am trying to learn more about Relational Databases and I figured there is no better way to learn then to actually do something. I decided to make a personal attempt to look at Personal Budget Accounting and Forecasting. I have done some research thus far and would like to get some insight on my current Database Design and Normalization.

What are your thoughts and suggestions on my current Database Design? I have included some information below to better help you help me 🙂

Disclosure: This is a personal project. Not for homework or for work.

Business Facts

  • A Bank ACCOUNT can have many ENTRIES

  • An ENTRY can either be a CREDIT or DEBIT

  • An ENTRY has a date it was credited on or debited on
  • An ENTRY has a single PAYEE

  • An ENTRY can be associated to a BUDGET CATEGORY

  • A CREDIT has an amount of the ENTRY

  • A CREDIT has a description of the ENTRY
  • A CREDIT can be scheduled in the future

  • A CREDIT can be reoccurring in frequency and or amount

  • A DEBIT has an amount of the ENTRY

  • A DEBIT has a description of the ENTRY
  • A DEBIT can be scheduled in the future

  • A DEBIT can be reoccurring in frequency and or amount

  • A PAYEE has a name

  • A BUDGET has many BUDGET CATEGORIES

  • A BUDGET can only be associated to a single calendar Month

  • A BUDGET CATEGORY can contain many ENTRIES

  • A BUDGET CATEGORY has a name

  • A BUDGET CATEGORY has a BUDGET amount

  • A FORECAST has a start date

  • A FORECAST has an end date
  • A FORECAST has a beginning balance
  • A FORECAST has many FORECASTED DAYS

  • A FORECAST has a single FORECASTED BUDGET

  • A FORECASTED DAY has a single date

  • A FORECASTED DAY can have many FORECASTED DEBITS

  • A FORECASTED DAY can have many FORECASTED CREDITS

  • A FORECASTED DEBIT has an amount

  • A FORECASTED DEBIT has a description
  • A FORECASTED DEBIT has a FORECASTED BUDGET CATEGORY
  • A FORECASTED DEBIT has a single PAYEE

  • A FORECASTED DEBIT can be reoccurring

  • A FORECASTED CREDIT has an amount

  • A FORECASTED CREDIT has a description
  • A FORECASTED CREDIT has a FORECASTED BUDGET CATEGORY
  • A FORECASTED CREDIT has a single PAYEE

  • A FORECASTED CREDIT can be reoccurring

  • A FORECASTED BUDGET has many FORECASTED BUDGET CATEGORIES

  • A FORECASTED BUDGET CATEGORY can have many PAYEES

  • A PAYEE has a name

Sample Data

+----------------+----------+------------------+----------------+---------------+--------------+------------------+
| Account Number |   Date   |   Description    |   Payee Name   | Credit Amount | Debit Amount | Budget Category  |
+----------------+----------+------------------+----------------+---------------+--------------+------------------+
|          25178 | 10/01/18 | Payroll          | My Work        | $1000.00      |              | Income           |
|          25178 | 10/02/18 | McRibs for Lunch | McDonalds      |               | $13.12       | Fast Food        |
|          25178 | 10/03/18 | Electric Bill    | FPL            |               | $133.68      | Electric         |
|          25178 | 10/04/18 | Water Bill       | City Water Co. |               | $58.12       | Water and Sewage |
|          25178 | 10/05/18 | Clothes for Work | Target         |               | $65.02       | Clothes          |
|          99875 | 10/28/18 | Bonus Check      | My Work        | $1300.00      |              | Income           |
+----------------+----------+------------------+----------------+---------------+--------------+------------------+

+----------+-------------+--------------+---------------+-----------------+------------------+
| Due Date |    Payee    | Debit Amount | Credit Amount | Budget Category | Re-Occurs On Day |
+----------+-------------+--------------+---------------+-----------------+------------------+
| 10/28/18 | Mortgage Co | $1500.00     |               | Mortgage        |               28 |
| 10/01/18 | My Work     |              | $990.00       | Income          |                1 |
| 10/03/18 | FPL         | $110.00      |               | Electric        |                3 |
+----------+-------------+--------------+---------------+-----------------+------------------+

Current Database Design

I figured it would be helpful to know WHY I did something so you can understand my logic and reasoning.

Revision4-Forecast

  • Each Budget can contain more then 1 Budget Category. I added an isActive column on both Budgets and BudgetCategories in case I wanted to reactivate a different budget or budget category.
  • I separated transactions into two very much alike split tables Debits and Credits as I saw there was two types of transactions.
  • In order to allow and track Scheduled or Reoccurring transactions I created a ScheduledTransactions table that allowed me to have two different amounts, an expected amount in ScheduledTransactions and an actual amount in either Debits or Credits.

Revision4-Main

  • I figured each forecast would need a start and end date as well as a starting balance.
  • Each day would need to be forecasted to be able to determine the sum of Debits and Credits.
  • I think I could have used the other tables and added a few isForecasted Columns and it would have worked the same. I decided not to go that route in order to decouple the two in case any changes would need to be made as well as if this was a large scale application reading and writing large forecasts into the same tables as actual transactions I would think would cause a log of performance issues.

Best Answer

More generally: I would START with a list of questions that I want to answer. Like:

Who am I paying the most? Did I pay the sewage-hauling bill this month? What are my cash requirements for this month? Will I need to go out and kill stuff for food?

The nature of these questions should drive the design of the schema.

That said, this schema looks pretty good.

I agree with the idea that the debits and credits could be in a single table.

Related Question