lundi 1 décembre 2014

Database design tracking daily personal expense


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!





Aucun commentaire:

Enregistrer un commentaire