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 manyENTRIES
An
ENTRY
can either be aCREDIT
orDEBIT
- An
ENTRY
has a date it was credited on or debited on - An
ENTRY
has a singlePAYEE
An
ENTRY
can be associated to aBUDGET CATEGORY
A
CREDIT
has an amount of theENTRY
- A
CREDIT
has a description of theENTRY
- A
CREDIT
can be scheduled in the future A
CREDIT
can be reoccurring in frequency and or amountA
DEBIT
has an amount of theENTRY
- A
DEBIT
has a description of theENTRY
- A
DEBIT
can be scheduled in the future A
DEBIT
can be reoccurring in frequency and or amountA
PAYEE
has a nameA
BUDGET
has manyBUDGET CATEGORIES
A
BUDGET
can only be associated to a single calendar MonthA
BUDGET CATEGORY
can contain manyENTRIES
- A
BUDGET CATEGORY
has a name A
BUDGET CATEGORY
has aBUDGET
amountA
FORECAST
has a start date- A
FORECAST
has an end date - A
FORECAST
has a beginning balance - A
FORECAST
has manyFORECASTED DAYS
A
FORECAST
has a singleFORECASTED BUDGET
A
FORECASTED DAY
has a single date- A
FORECASTED DAY
can have manyFORECASTED DEBITS
A
FORECASTED DAY
can have manyFORECASTED CREDITS
A
FORECASTED DEBIT
has an amount- A
FORECASTED DEBIT
has a description - A
FORECASTED DEBIT
has aFORECASTED BUDGET CATEGORY
- A
FORECASTED DEBIT
has a singlePAYEE
A
FORECASTED DEBIT
can be reoccurringA
FORECASTED CREDIT
has an amount- A
FORECASTED CREDIT
has a description - A
FORECASTED CREDIT
has aFORECASTED BUDGET CATEGORY
- A
FORECASTED CREDIT
has a singlePAYEE
A
FORECASTED CREDIT
can be reoccurringA
FORECASTED BUDGET
has manyFORECASTED BUDGET CATEGORIES
A
FORECASTED BUDGET CATEGORY
can have manyPAYEES
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.
- Each Budget can contain more then 1 Budget Category. I added an
isActive
column on bothBudgets
andBudgetCategories
in case I wanted to reactivate a different budget or budget category. - I separated transactions into two very much alike split tables
Debits
andCredits
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 inScheduledTransactions
and an actual amount in eitherDebits
orCredits
.
- 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.