Database structure proposal

[nathan-hello]

I made this mockup today, mostly while at work. Merry Christmas.

https://dbdiagram.io/d/63a904107d39e42284e77729

This is a map of below. I haven't worked well with complicated relationships like what I'm about to explain, so apologies if the lines are wrong. I hope you get the idea. Also the types aren't meaningful in the picture, I just picked something.

This program, like most programs definitely fits into the "Create/View/Modify" structure of programming. We create the data, view it, and modify it. This is about what there is to create, which if you think about it is the entire mission statement of the program. Nobody asked for me to do this, but I thought hawk's structure needed some tweaking, and while doing that I thought of some ideas.

SQL Organization

• An asterisk (*) indicates that the value is nullable. The absence of an asterisk means that a value must be present.
• The key is used for lookup purposes. It is a value that should not be changed. It could be called "id" instead.
• For the most part, types are not given. If we use SQLAlchemy columns, they are a little different than basic SQL types.
• Writing this made me understand the scope of the project. This was really fun, I have imagined more ideas and things that could make it unique, useful, and fun to program.
• I got rid of functions of the previous database writeup. I think I found adequate replacements for that which I got rid of. I could be wrong. I could have also missed a key feature that hawk has been imagining for months but I haven't considered or heard of. Please include what I've missed.

user

Purpose: Identify users. Users should be able to share categories with other users.

• key
• username
• display_name (*)
• associated_users (*): Used for two people who have separate accounts but want to see their data in aggregation.
• associated accounts (*): The database ID of accounts associated with a user.
• trees: A tree is the map of categories and their relationships. A user can import/export their trees. Trees can be shareable. The one that is currently displayed on the tkinter program is the tree. Trees should be stored in json.

category

Purpose: Group accounts together for frontend visualization and for backend data visualization/budgeting calculations. There should be a default category that accounts are placed in if they are not associated with a category or if their category is deleted.

• key
• user_associated
• display_name
• account_associated (*): Accounts within the category.
• parent_id (*): Root categories are null. Children have their parent's key as a value here.
• children_ids (*): Instead of going from the bottom of the tree up, we can use this to do calculations from the top of the tree down.
• keywords (*): Used for search? I'm not sure what the keywords table was for.

account

Purpose: Accounts are used for representing their balance, understanding where a ledger_entry came from, and most importantly, point the overall program to the algorithm that understands their monthly statement (or however we're extracting information)

Users should be able to share their account with another user.

• key
• users_visible: A list of users that can see the account. The user who creates it shouldn't be allowed to remove themselves without giving it to someone else.
• visibility: "shared" or "private". Used as a UI indicator to remind someone of the privacy setting. When the status changes from private to shared, remove all other users from users_visible. Alternatively, this could work the other way around.
• display_name
• account_type: Should be a string. We're probably using it in statements like `if account_type == 'value'`, and I'm not sure how an integer would be helpful in this case.
• balance_amount (*): If it's a loan or credit, then it's the amount of outstanding loan/credit card balance. If it's a checking or investment account, it's the balance. Nullable because we have to set up the account before we can specify a balance.
• algorithm (*): Which alogorithm is capable of processing this account's statement. If null, no algorithm applies.
• balance_updated_date (*)
• institution_name (*): Could potentially be used to group accounts by bank.
• savings_goal (*)

ledger_entry

One row in this table represents one transaction from an account. This should be the table into which all sorted files are pushed. Investments could potentially have their own table, but I'm not sure if that is justified. Someone might want to classify something as an investment that isn't obvious to any scripts we write.

• key
• account
• date
• name: This is the technical name as stated on a monthly statement. After a user manually associates the display name, we use it to compare subsequent transactions.
• display_name (*). Nullable because if a user wants, they can choose not to give wacky statement entries a nickname.
• category (*): When null, ledger entries go into a default category.
• value (*): The amount of money. Investments are no different than regular purchases; they subtract from your bank account and should be accounted for in a budget. If null, ask the user how much the transaction was or whether to delete it from the database (unless there is some justification for having entries with null values in the 'values' column.)
• description (*)
• note (*)
• ticker (*): For investments. Again, they could potentially have their own table, but I'm not sure if it's necessary.

spending_limit

One spending_limit entry represents one category to which it applies. There is a toggle for whether the budget should apply to that category's children. If a category has multiple inherited budgets, they can be resolved by making all of the limitations apply. Savings goals are not a part of this table; they are a column in the "account" table.

Alternatively, we could resolve budget conflicts in some interesting way. This could be where a budgeting scripting language could exist. There is very interesting potential here, I think.

• key
• category
• amount
• applies_to_children: Bool. Imagine HEALTH leads into GYM. We want to put a cap on HEALTH because we buy too many vitamins, but not GYM because that's a fixed cost that we have accepted. Making this false means HEALTH will have the limitation but GYM will not.

[andersbandt]

Wow. Just wow. I appreciate all you effort on this writeup.

I think you did a good job of capturing my intents with the program. I have a few comments on this that I will get around too later.

[andersbandt]

Haha I feel very compelled to respond to this awesome write up so here's my comments :)

• Love that you added a tree column to the user table. I think the ability to import/export and share those trees will be super helpful
• Good additions to the category table. I'm curious how easily the algorithm to add all the children categories will be.... It just seems to introduce some complexity to managing the database. For example - adding one category we will have to traverse up the tree and update the database for each category. It seems to me like we would want to keep our database as minimal as possible
• I don't think balance_amount should be a single value in the account table. I would really like to track balance history which would require a ledger of balances
• Love the savings_goal addition!!!
• Slightly confused on the name and display_name for the ledger table
• I'm also on the fence between combining investment data and regular transactional data... I can see a few considerations
  • Less data clutter to deal with when they are separated
  • Easier to get a comprehensive view of how money moves when they are integrated
  • Better investment analytics can probably be performed if they are separated. There's some qualities about an investment that I think would warrant some more columns (investment type - stock/bond/other/etc)

Again, great job on this.

[nathan-hello]

adding one category we will have to traverse up the tree and update the database for each category

Imagine we want to click on HEALTH and see its spending and the spending of all of its children separately. Or, we want to "collapse" a category in the UI. It makes sense to me to track both ends of the relationships because sometimes we'll want to do things from the bottom up, and other times we'll want to do things from the top down.

I would really like to track balance history which would require a ledger of balances

Agreed

Slightly confused on the name and display_name for the ledger table

Here's how I imagine UX is going to be for setting up:

• User points program to file
• A transaction that is uncategorized is detected, it gives user a prompt to categorize it and rename it
• User puts into category (if not, it is uncategorized), and renames (if not, it retains the name from the document)

Here's a New York Times charge on my card: NYTimes*NYTimes disc 800-698-4637 NY If I got that prompt, I would put it under MEDIA, and rename it NYTimes

I'm also on the fence between combining investment data and regular transactional data

Yeah, I think so too. The table doesn't really help with balances or history, which an investment account needs. Should we have a investment_account and a investment_ledger_entry? Or can the investment accounts be tracked in the accounts table?

[lordlabakdas]

Awesome work, @nate-llc

I took the liberty to extend/modify on @nate-llc 's db design and it can be seen at https://dbdiagram.io/d/63a9de987d39e42284e79708

Some changes a summarizd below:

• use of UUID for primary keys as opposed to int values.

• adoption of user table as a base table (no external dependencies)

• trees column in my opinion can be implemented inside of code (via joins). If we add it inside of db, then we might have to insert/update multiple tables.

• The same logic applies to children_list as we would have to modify tables every time there is a new value. Instead, we just join inside code when necessary.

Of course, the above argument of handling it inside of code assumes that the children_list and the trees column is required by the front end only sporadically. If these fields are used frequently, better to handle it inside the database, potentially using a db trigger.

• I also added a couple of tables (roles_and_permissions, account_roles) to achieve the connected_users functionality. An example of a role is Primary User. An example of permissions is ["Read", "Write", "Delete"]. A second user can have as a role secondary account user with permissions ["Read"]. Do let me know your thoughts on this.

• Ideally, we would want to have lookup tables for institution name, permissions, and currency. Let me know if we would like to consider this approach.

[nathan-hello]

I appreciate the kind words and discourse around this post. Thank you all.

trees column in my opinion can be implemented inside of code (via joins). If we add it inside of db, then we might have to insert/update multiple tables

I didn't realize this, but yes, if we were to import/export trees, then that would affect the entire category table for that user. My reservation around constructing the tree from the category rows is that I'm not sure how you would make this exportable.

Having this kind of functionality isn't something I expect we're going to be doing any time soon. The important thing is that we construct the db in such a way where we can add this later. We don't want the twitter "in order for people to edit tweets we have to recreate the entire database" problem.

You seem like a better authority than I on this. I would just ask for an explanation on how someone could have multiple trees (like profiles) that they can swap around to, export, etc, if the tree if generated by the categories composing it.

to achieve the connected_users functionality

Thank you. Something I didn't mention in the original post is that I assume there would need to be more points of security than what I described.

Ideally, we would want to have lookup tables for institution name, permissions, and currency

I'm not sure how having a lookup table for 1:1 attributes like these would be more useful. I'm very much open to being convinced.

[nathan-hello]

https://dbdiagram.io/d/63aa5a897d39e42284e7a17a

Thank you hawk and labakdas for the discourse.

Changes to outline:

• Changed ledger_entry to transactions. Now that the table is only used for spending and not spending and investments, a better name is warranted.
• Added investments table. The data associated with them is similar but the scope is very different to spending.
• Changed relationship of accounts -> transactions and accounts -> investments to one-to-many.
• Added one-to-many relationships from categories.keywords_associated to transactions.keywords and investments.keywords. I actually can't get my head around how this relationship works, so either what's on paper is flawed in some way or I don't understand something fundamental to sql. Should this be many-to-one? I'm not sure.

I also moved the boxes around. I would have liked to make the link an edit link, but you'd have to pay for the site to do that. The site seems pretty robust, allowing you to export what you make in plain SQL, Postgres, etc, so it might be worth. Or we rewrite it using the SQLAlchemy ORM. I would imagine learning enough sql to make the chart 'perfect' and exporting it would be easier.

But yea, that's the next draft of the tables.

[andersbandt]

It's looking really good, thanks for making those updates. I love that website too, really really nice for collaborating on this. I think we are getting closer and closer to a final database structure. I agree that the keywords relationship is a hard one to deal with. I'll let this draft sit with me and jot my next thoughts down when I have my chance.

I appreciate you spearheading the effort to get this locked in. This definitely does need to be the first priority, as the rest of the application will depend heavily on the structure of this database.

[lordlabakdas]

@nate-llc Personally, I do not think transactions and investments need a keywords field. They get it from the category table.

I assume account_id foreign key is UUID, similar to the id field in accounts.

We dont need an export from dbdiagram. SQLAlchemy will create out tables for us. https://docs.sqlalchemy.org/en/14/core/metadata.html

[lordlabakdas]

If we want to be fancy, possibly use a ulid instead of uuid. https://github.com/ahawker/ulid

[nathan-hello]

I do not think transactions and investments need a keywords field. They get it from the category table.

Unless I'm missing something, I don't think we could recover the keyword for a transaction if its corresponding category were deleted? This is why I think it's better to assign the keywords to the transactions/investments and allow the categories to find them

[lordlabakdas]

Excellent point, @nate-llc

If we decide to move the keywords to transactions, how do we populate the keywords? Still based on a category?

[nathan-hello]

A keyword is just a vehicle for tagging transactions so you can put groups of transactions (the group defined as whatever keyword they have in common) into a category. If there's another use for them, or if this isn't at all what @andersbandt was thinking, I'm more than willing to listen, but this is the impression I got from the discord convo the other day

Using the program, a user throws in their statement with transactions and the program gives them a prompt for every transaction that isn't already in the db. They can either put a keyword onto it (and if it's a new keyword, they would have to put that keyword into a category) or put it in a category directly

Tags are useful because if someone wanted to move their tag streaming service from ENTERTAINMENT to SUBSCRIPTIONS, they would move all of that tag in one action

[andersbandt]

re: keywords discussion I agree with Nate's last comment in the Discord that keywords could take the form of some intermediate table between the categories and transaction in the ledger. Here are a couple reasons I think that will be best

1. we are unsure of the exact form that automatic categorization through keywords will take place. I think that restructuring only the keywords table would be easier than possibly having to restructure our ledger or categories tables if we choose to include keyword information in them
2. This will possibly solve the problem of categories being deleted. Data would be retained in the keywords table.

A sample table structure could be like below???

id UUID category_id UUID keyword_strings JSON

I'm curious to hear what Nate was thinking would be the advantages of this method as well. And anyone else's thoughts.

[nathan-hello]

Sorry for the delay, I was gonna respond in discord but my response got kinda long

relevant discord :

category: healthcare Category keywords: pharmacy, hospital Transaction keyword: Tylenol, 1 main st, mom and pop store

Trying to square this circle, what I've come to is that what you're calling "transaction keyword" here is data that I wouldn't consider algorithmically relevant. if we have transactions > keywords > categories in a many:few:1 format, which is what I'm proposing, we can't have what you're calling transaction keywords here.

I now understand your goal in having multiple "sub"-keywords. Multiple keywords on a transaction is a major shortcoming that I was willing to say "this is a limitation: one keyword per transaction". Also the goal of pre-loading keywords can't happen if they're attached to transactions, because then we would have to pre-load transactions, which for the time being doesn't make a lot of sense.

My issues with having keywords based on category still stands, I think. I think your solution would work but there are the following restrictions.

• Any preloaded keywords would have to be on corresponding categories, so if you delete a category and its keywords weren't in another category, the keywords go too. I would say making undeletable categories isn't something that we can accept. Both because it's not a good UX and because it speaks to the difficulty on potentially changing it down the road.
• Conflicts in a transaction having multiple categories find the same keyword would be expensive to prevent. Maybe a user wants the transaction to fill two separate categories. It's this kind of control over data processing that I think is missing from the finance apps out there. Again, keywords on transactions would almost be worse at this because of the one-keyword limitation
• I'm not sure how having different keywords for category and transactions will work. How does Tylenol map to pharmacy?
  • I was going to ask about this but didn't. Even with the mapping solved, differentiating between a transaction within a category with one keyword, and one with a different keyword should be a goal

I need some help with my solution, however. I can't quite get my head around the goals of the multiple keywords and from what I do understand, I'm not sure how to map.

The following are all the goals that I understand there to be. If we disagree on the goals or if there's something I'm missing, that's the more important thing to be talking about

Here's what I've got:

• Allow for a user to append multiple keywords to a transaction.
  • Question: How do we determine relevance? Should there be a singular keyword for categorization and the rest for helpful data in some other way? I tried thinking of a solution with this kind of structure and couldn't crack it. I'm very much open to discussing it though.
• Upon data entry (please comment if the data entry process should be different than this): User puts on keyword(s) and if they choose, manually put transaction into a category.
  • This is where having multiple keywords is most confusing for me. In this sense, a keyword is used as a middle ground. How could we have multiple middle grounds?
• If a category were deleted, its keywords are still attached to the transactions.
• Ability to move all of one keyword to another category. Just as keywords are helpful for data entry, they should be helpful for organization after data has been collected.
• Any default data should be treated the same way as user-generated data.

Solution: A keywords table. I'm not sure how the original keywords table was mapped and used in the original program, I'm very much curious what hawk thought about before all of this. It seems like if we want to treat keywords as a middle ground, then it makes sense to have a table between transactions and categories.

https://dbdiagram.io/d/63af64bf7d39e42284e84137

This is the only map I could think of, and I've tried. There must be some contradiction in the goals that I haven't understood yet or I don't know sql well enough. If this doesn't seem worth to anyone else, I can go with keywords in the category table. I just don't see it

[lordlabakdas]

My thoughts: Allow for a user to append multiple keywords to a transaction. Question: How do we determine relevance? Should there be a singular keyword for categorization and the rest for helpful data in some other way? I tried thinking of a solution with this kind of structure and couldn't crack it. I'm very much open to discussing it though. To answer this question, this is how I view it. Categories have a set of keywords. If system category, comes with a set of pre-filled static keywords. If a custom category, user fills keywords during creation or after or both. Similarly, transaction have its own set of keywords which can have an intersection with category keywords in some cases. Transaction keywords can be manually added by the user, in which case we assume relevance or auto-populated by the system (maybe later, once we have a robust dataset of transactions and verified categories) possibly by using NLP or similar methods. Investments follow the same logic with having their own set of keywords.

This is where having multiple keywords is most confusing for me. In this sense, a keyword is used as a middle ground. How could we have multiple middle grounds? The resulting keywords is the union of the category keyword and transaction keyword. Ideally, this must be a unique combination of keywords but it might also be common to have overlap with other transactions.

If a category were deleted, its keywords are still attached to the transactions. True in the above case.

Ability to move all of one keyword to another category. Just as keywords are helpful for data entry, they should be helpful for organization after data has been collected. True in the above case.

Any default data should be treated the same way as user-generated data. There is a difference. Default data is shared amongst users and hence non-deletable. User defined data can be deleted as it is present only in the particular user view.

Any preloaded keywords would have to be on corresponding categories, so if you delete a category and its keywords weren't in another category, the keywords go too. I would say making undeletable categories isn't something that we can accept. Both because it's not a good UX and because it speaks to the difficulty on potentially changing it down the road. Keywords are present in both categories and transactions. If categories are deleted, transactions can be identified using transaction keywords.

Conflicts in a transaction having multiple categories find the same keyword would be expensive to prevent. Maybe a user wants the transaction to fill two separate categories. It's this kind of control over data processing that I think is missing from the finance apps out there. Again, keywords on transactions would almost be worse at this because of the one-keyword limitation It depends on how we handle conflicts. Either we make conflicts mutually exclusive or we dont duplicate categories and allow for duplicated keywords inside categories. This is a rule we add to the system.

I'm not sure how having different keywords for category and transactions will work. How does Tylenol map to pharmacy? As a parent and a child. Parent keyword (category keyword) is pharmacy has a child keyword (transaction keyword) Tylenol.

[nathan-hello]

It sounds like all of my concerns are accounted for. I would like to revisit the difference between system-generated content and user-generated content, but we can certainly table that for when we go to actually write it. I imagine what you're thinking of is good, and if hawk is okay with all of this, I say send it.

My only request is to do a final draft on the db map, and then we can get to work on actually writing it

[andersbandt]

Thanks for creating this issue on database structure @nate-llc . The drafts you and @lordlabakdas made were great, along with the great discussion. This really cemented the idea that working "from the bottom up" is the best approach to tackle this new iteration of the financial application.

[nathan-hello]

https://dbdiagram.io/d/63aa5a897d39e42284e7a17a

Potential final draft. I edited the db diagram to include the keywords table. There's the connection from transactions/investments to category id in case of manual sorting. Does this look good? Specifically with the many/one relationships. @lordlabakdas

[lordlabakdas]

Awesome, @nate-llc

We can also add additional fields such as created_at and updated_at but can do that as we work on the implementation of the models.

[lordlabakdas]

Will close this issue. Can reopen if there is additional inputs/questions from members