| Entity ID | Entity Name | Description | Entity Type | Primary Key(s) |
|---|
| 1 | transactions | Transaction Fact Table One line per transaction, but only transactions that have actually happened. | gold | transaction_id |
| 2 | scheduled_transactions | Scheduled Transaction Fact Table One line per transaction, but only transactions that have not actually happened. | gold | scheduled_transaction_id |
| 3 | accounts | Accounts Dimension Table Holds all the information relating to an account, type 1, 1 line per account. | gold | account_id |
| 4 | categories | Categories Dimension Table Holds all the information relating to a category, type 1, 1 line per category. | gold | category_id |
| 5 | payees | Payees Dimension Table Holds all the information relating to a payee, type 1, 1 line per payee. | gold | payee_id |
| 6 | dates | Good old fashioned Date Dimension | gold | date_id |
| 7 | accounts | Silver Accounts Table Other random information. | silver | |
| 8 | categories | Silver Categories Table Other random information. | silver | |
| 9 | payees | Silver Payees Table Other random information. | silver | |
| 10 | scheduled_transactions | Silver Scheduled Transactions Table Other random information. | silver | |
| 11 | transactions | Silver Transactions Table Other random information. | silver | |
| 12 | accounts | | bronze | |
| 13 | categories | | bronze | |
| 14 | payees | | bronze | |
| 15 | scheduled_transactions | | bronze | |
| 16 | transactions | | bronze | |
erDiagram
transactions {
string transaction_id PK
int account_id
int category_id
int payee_id
int date_id
decimal amount
boolean cleared
boolean approved
boolean deleted
string memo
string flag_color
string transfer_account_id
}
scheduled_transactions {
int scheduled_transaction_id PK
int account_id
int category_id
int payee_id
str date_first
str date_next
decimal amount
string frequency
boolean deleted
text memo
string flag_color
str transfer_account_id
}
accounts {
int account_id PK
string account_name
string account_type
boolean on_budget
boolean closed
text note
decimal balance
decimal cleared_balance
decimal uncleared_balance
boolean deleted
}
categories {
int category_id PK
string category_name
string category_group_name
boolean hidden
text note
decimal budgeted
decimal activity
decimal balance
boolean deleted
}
payees {
int payee_id PK
string payee_name
boolean deleted
}
dates {
string date_id PK
date date
int year
int month
int day
boolean is_weekday
int weekday
}
transactions ||--|| payees : "payee_id"
transactions ||--|| dates : "date_id"
scheduled_transactions ||--|| payees : "payee_id"
transactions ||--|| categories : "category_id"
transactions ||--|| accounts : "account_id"
scheduled_transactions ||--|| accounts : "account_id"
scheduled_transactions ||--|| categories : "category_id"
%%{init: {"flowchart": {"defaultRenderer": "elk"}} }%%
flowchart LR
1[1 gold transactions]
2[2 gold scheduled_transactions]
3[3 gold accounts]
4[4 gold categories]
5[5 gold payees]
6[6 gold dates]
7[7 silver accounts]
8[8 silver categories]
9[9 silver payees]
10[10 silver scheduled_transactions]
11[11 silver transactions]
12[12 bronze accounts]
13[13 bronze categories]
14[14 bronze payees]
15[15 bronze scheduled_transactions]
16[16 bronze transactions]
11 --> 1
10 --> 2
7 --> 3
8 --> 4
9 --> 5
12 --> 7
13 --> 8
14 --> 9
15 --> 10
16 --> 11