9

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.
Improve this question
7
  • just a thought do you require metadata columns in each table like when the row got inserted, who inserted it, when last updated, who updated it, which process etc?
    – Biju jose
    Nov 5, 2018 at 5:21
  • Since this is for personal development use I wont need any of that.
    – Jon H
    Nov 6, 2018 at 1:18
  • 1
    The schema and normalisation rules depend upon how you choose to model the problem. Traditionally you'd have separate tables for actuals, budgets and forecasts. But you could have them all in a single table with an attribute or flag to denote the difference. Same with DR/CR. Same table but use +/- values to indicate I/O. Once you move into double entry accounting you'd need both sides of an equation to balance and create accounts. Are you going that far?
    – Sir Swears-a-lot
    Nov 6, 2018 at 3:31
  • 1
    I think this is a very ambitious project to tackle for the purpose of learning about databases. Db Schema design and normalisation is one thing, accurate record keeping is another, accounting and forecasting is even more complex again. Which is your priority?
    – Sir Swears-a-lot
    Nov 6, 2018 at 3:35
  • @SirSwears-a-lot Yeah I am not going that far. Think about just being able to track your personal finances into various budget groups and be able to project simple what if scenarios like... What if I got a new car payment and went on vacation 4 months out? What would my income to debt ratio look like on a week to week or month to month frequency. Or What if I changed my Grocery Budget from 500 to 600?
    – Jon H
    Nov 6, 2018 at 4:49

2 Answers 2

Reset to default
2

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.

Improve this answer
1

I would identify the payees, in terms of budget and type of account. Say you need to list or consult the payees. I would also have an active column for payees. It could be nice to know what account pays what budget in the future.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.