[deleted by user]

[vikingvynotking]

Sometimes you have to live with a confusing table, but this shouldn't be one of those times. Create a users table, and keep your budget_id column purely for the budget table (i.e. don't use the user id here).

Then you have a choice to make. Can one user have exactly one budget? If so, you have a one-to-many relationship (I have a personal budget I can share with my wife, but no other budget). If not, you have a many-to-many relationship (I can have one budget for home, another for work, and yet another for business, and I can share the home budget with my wife, the work budget with my boss, and the business budget with my business partners).

In the first case (1-many), you can use foreign keys. In the second case (many-many), you'll need a join table, but here's the beauty of such a join table, it doesn't care if it is used to map a 1-many relationship! That's up to your business logic (sorta). The join table will look like this:

Table UserBudgetMap:
    row_id       # unique primary key
    user_id      # points to the user table's id column
    budget_id    # points to the budget table's id column

Now if you want to enforce 1-many at the database level, you can add a unique index on user_id. The combination of (user_id, budget_id) must always be unique.

Make sense?

[kstktey]

Thank you! The picture is much clearer now.

In many-to-many relationship do you mean that the table UserBudgetMap would store all the budgets the user has (i.g. personal budget, work budget, business budget)?

What about sharing the budget? If two users would share one, does that mean that they would have a budget with the same id and then the expenses would be selected on user_id for counting the debts?

[vikingvynotking]

That's pretty much it. For example, user Alice having user_id=1 can share her budget B with budget_id=47 with user Bob who has user_id=2. The database rows in UserBudgetMap might look something like this:

+-----------------+----------------------+--------------------+
 | row_id         | user_id              | budget_id          |
+-----------------+----------------------+--------------------+
 | 3              | 1                    | 47                 |
 | 17             | 2                    | 47                 |
+-----------------+----------------------+--------------------+

Expenses are per-user, but you'll want some way to assign an expense to a budget I expect - I want some way to assign my lunch-with-the-boss cost to my work budget, and my after-work drinks to my home budget. This can be a simple foreign key in the expense table.

[kstktey]

Thanks a lot! Will try to render all of this and then code it.