Help with database design

[Concentric]

Im in the process of building a personal finance tracking app.
Im looking for a solution for one part of my database.
Each category is linked to multiple transactions (1:m) by way of a category_id fk in each transaction.
Each category is also linked to multiple budgets. That is each category will have a new budget for every month.
Im not sure how to define the relationship between the category and its budgets.

Ive the best ive come up with is a unique id per budget with a category_id fk and then the budgeted amount.
Is there a better way?

TIA

Edit: just realized that i posted in the wrong software section.
@mods if you can move it please

 

[Urist]

Sounds ok, but don't you want to link a transaction to a budget item and get the category from there?
Give us the attributes of each table and we'll tell you if it's normalised enough.

 

[Concentric]

@Urist
Apologies
I missed the notification for this and only saw it now.

This is what i currently have.
I thought about using a composite key of the category_id and month.
What do you think?

 

[Urist]

Sure, if you only want one budget per category per month.
Instead of month i`d have a start and end date in date format, wouldn't use that as the key then but rather a surrogate. If you still want to force uniqueness you can use a unique index on multiple columns.
Would be easier to build reports and queries to view transactions vs. budgets within a certain time period. You can still auto-fill the month with a date picker tot he start and the end of the month.

 

[maumau]

Sure, if you only want one budget per category per month.
Instead of month i`d have a start and end date in date format, wouldn't use that as the key then but rather a surrogate. If you still want to force uniqueness you can use a unique index on multiple columns.
Would be easier to build reports and queries to view transactions vs. budgets within a certain time period. You can still auto-fill the month with a date picker tot he start and the end of the month.

I love it when you speak foreign

 

[Luke7777]

Budgets are normally per financial period and not from a date to a date. Transactions are also for that period and not necessarily from the 1st tot the last day of the month, so diagram above should be fine. I would just add the period (month) to the transaction table

 

[Concentric]

Budgets are normally per period and not from a date to a date. Transactions are also for that period and not necessarily from the 1st tot the last day of the month, so diagram above should be fine. I would just add the period (month) to the transaction table

Why would i need both a month and a date ?

 

[Hamster]

Side note: you’ll want a many to many relationship between transactions and categories.

An example, if I pay extra into my bond it can be classified as both debt repayments and saving.

Dinner can be both a luxury and food.

Silly examples but I’d design it this way just in case.

 

[Kosmik]

Why would i need both a month and a date ?

Its pretty common financially, normally the first day of the month and its used to group the transactions into a financial period. You could run a group by with a date part type when querying against your transactions date but a separate column stored once and indexed for a period will be better performance wise.

 

[Urist]

Budgets are normally per financial period and not from a date to a date. Transactions are also for that period and not necessarily from the 1st tot the last day of the month, so diagram above should be fine. I would just add the period (month) to the transaction table

Would add a lookup table for financial period that still works with dates then.

 

[Urist]

Side note: you’ll want a many to many relationship between transactions and categories.

An example, if I pay extra into my bond it can be classified as both debt repayments and saving.

Dinner can be both a luxury and food.

Silly examples but I’d design it this way just in case.

Would then be difficult to link the expenditure to the budget as the relationships is currently.

 

[Hamster]

Would then be difficult to link the expenditure to the budget as the relationships is currently.

Budget 1 : N Transaction 1 : N TransactionCategory N :1 Category

Categories are nothing but metadata. A budget for a period (instance) has transactions. So you have the budget totals and category totals. You also have category totals over multiple budget periods.

 

[Luke7777]

Why would i need both a month and a date ?

The date will be the transaction date. Month will be the financial period of that transaction. Financial periods doesn't always follow a calendar month. You can't determine from the transaction date in which period the transaction happened. That's definitely the case in commercial applications, but this being a personal finance app, nothing what I've said so far might be applicable In your case transaction dates might very well directly relate to your budget period and you won't need the month.

 

[Urist]

Budget 1 : N Transaction 1 : N TransactionCategory N :1 Category

Categories are nothing but metadata. A budget for a period (instance) has transactions. So you have the budget totals and category totals. You also have category totals over multiple budget periods.

I think he wants to track expenditure vs. a budget within a certain category and time period. Giving multiple categories would duplicate the reported expenditure. That's why I also recommended linking the transaction directly to the budget instead of through the categories like in his diagram. If an expenditure is unbudgeted or unplanned have that as an item with R0 in your budget, or make provision.
I agree you can have multiple categories for an expenditure, but not of he wants to use it to link expenditure to budget through category.

 

[Hamster]

he wants to use it to link expenditure to budget through category

My brain just doesn’t want to accept this...but, whatever