Database design tracking daily personal expense

database-design

I currently have the following in Excel tracking daily expenses:
here

I would like to turn the following excel sheet into a normalized database. I would also like to capture the descriptions for each expense if its populated. I have 2 approaches in mind:

1) Create a date table and create a separate table for each expense category (as well as a separate corresponding expense description table for each table):

**Date:** 
id | date       | 
-----------------
1  | 2014-12-01 | 
2  | 2014-12-02 | 
3  | 2014-12-03 | 
4  | 2014-12-04 | 

**Rent:**
id |date_fk | amount | 
----------------------
1  | 1      | 1000   | 

**Restaurant:**
id |date_fk | amount | 
----------------------
1  | 2      | 12     | 

**Restaurant_desc:**
id | restaurant_fk | description        |
------------------------------------------
1  | 1             | Dinner at Arby's|

2) Create a date table and one general expense table and one expense description table:

**Date:** 
id | date       | 
-----------------
1  | 2014-12-01 | 
2  | 2014-12-02 | 
3  | 2014-12-03 | 
4  | 2014-12-04 |

**Expense:**
id | date_fk | amount | category     |
--------------------------------------
1  | 1       |  1000  | rent         | 
2  | 1       |  120   | parking      |
3  | 2       |  12    | restaurant   |
4  | 2       |  10    | entertainment| 

**Expense_description:**
id | expense_fk | description       |
-------------------------------------
1  | 3          | Dinner at Arby's  |

Which approach is better? I'm thinking the 2nd approach is better because its more flexible, if there are additional categories in the future, you won't have to add more tables to accommodate it. Or is there is a better schema for this? please let me know.
Thanks!

Best Answer

I would use a variation of your second approach:

**Expense:**
id | date       | amount | category_fk  | description      |
------------------------------------------------------------
1  | 2014-12-01 |  1000  | 1            |                  |
2  | 2014-12-01 |  120   | 2            |                  |
3  | 2014-12-02 |  12    | 3            | Dinner at Arby's |
4  | 2014-12-02 |  10    | 4            |                  |

**Category:**
id | category_name |
--------------------
1  | rent          |
2  | parking       |
3  | restaurant    |
4  | entertainment |

I don't think you need a separate table for dates. You gain nothing by doing that, unless you want to store extra information for that date, and even then, the date itself would be a natural key for that.

The description of a specific expense has a 1:1 relation with that expense, so it doesn't need a table of its own, unless you want to reuse descriptions (at which point you no longer have a 1:1 relation).

The category needs a separate table, since that will not have a 1:1 relation but a 1:n relation (a single category will be used on several expenses).

If you want to model a n:n relation, where an expense can belong to several categories as well, you will need a cross-reference table like so (removing the category_fk column from the expense table):

**Expense_Categories:**
expense_fk | category_fk |
--------------------------
1          | 1           |
2          | 2           |
3          | 3           |
3          | 4           |
4          | 4           |

As you can see, the Dinner at Arby's is considered both 'Restaurant' and 'Entertainment'.

Related Question