Transaction Table in Personal Finance App

relational-theory

I'm a noob designing a Personal Finance App to teach myself Rails. This is just for learning purposes, but I actually intend to use it to replace my current system in Google Spreadsheets. I want to apply best practice.

I created a transactions table with the following columns:

user_id: integer
type: string (income or expense)

date: date
account_group_id: integer
account_id: integer
amount: integer
(my currency does not need decimals)
comment: string

Now, the way is this designed, all users' transactions would be recorded on the same table. Is this a good idea?

Or should I create a different transactions table for each user?

Does it matter if I do it either way?

Best Answer

My first advice would be to check out a few Open Source projects on the same topic.

Adapt and adopt the concepts you like - pick and choose ideas. No point reinventing the wheel! :-) I appreciate that you want to learn, but translating code from [PHP | Python | Perl | Whatever] could also be a beneficial learning experience! And you might even gain tips and tricks from more experienced programmers!

Related Solutions

Table Design – Identical tables for different statuses

In my experiences with similar things (tables of scheduled jobs and tasks), everything is in one table. There is usually a status column, but jobs of all statuses are in the same table (sometimes there's an index on status). As for primary key, why not just use a sequential ID? I've seen that work very well. You can also have as combined key of draft number AND actual job number, unless there's some other requirement I'm not understanding.

If you think this will improve performance, make sure you have measurable problems that can only be solved this way before you make any changes. Also try to prove that this method results in actual necessary performance gains.

Referencing a Join table

To model the classical student - class - prof situation, you need to reverse your model. class can implement the n:m relationship between student and prof. To be precise:

[student] m -- n [class] n -- 1 [prof]

The n:m relationship between student and class would be implemented by another table. Like [participant]:

[student] 1 -- n [participant] n -- 1 [class] n -- 1 [prof]

A prof has many classes. A class has many participants. A student is participant in many classes. Student and prof are only linked by participation in classes in this model.

Generally, just because a student is linked to a professor in some way does not mean the student participates in all classes of the professor. That's an unwarranted over-interpretation. What a relationship between two entities actually means is defined by additional meta-data, usually indicated by the column names used.

Best Answer

Related Solutions

Table Design – Identical tables for different statuses

Referencing a Join table

Related Question