Say I have want to create a database for expenses.

I will have a table for holding Person details, and a table for holding Expense details.

Then there will be two many-to-many relationships between the Person and the Expenses tables. Person Can pay an expense, and a Person can use an expense.

What is considered better? Having one relationship table to hold both "paid for" / "used by" fields. Or two have two separate tables containing each of these fields?

Two parallel tables seems more correct, but I could also see it making the SQL more complex. Is there any compelling reason to use one design over the other?

  • 1
    It makes the SQL more complex only when you are asking the question "what are all the connections between person P and Expense E" - how often are you asking that question? It seems to me that questions relating solely to paying, or solely to incurring, will be much more common, and for those questions separate tables make more sense.
    – AakashM
    Commented Mar 18, 2014 at 9:38

1 Answer 1


If both fields are relevant in one record (i.e. "used by" and "paid for" can both have a person_id from the persons table), a single table is a better solution. If at least one of the two fields is always NULL, splitting the table would actually make the SQL less complex. (Also, if an expense is either "paid for" OR "used by" but not both, you could instead use an expense_type field instead of 2 separate fields).

As a sidenote, I would recommend against using space in field names.

  • I am aware there are many different ways that I could implement this, I am looking for opinions on why one way may be considered a best practice. Not sure I like the expense_type option, as that will essentially require an extra where clause on every statement, and I don't see any real advantage to being able to query them all as one. Commented Mar 18, 2014 at 10:46
  • 1
    If you're using one table with two fields you'd have to filter as well, but you'd have to filter different fields (for NULL) instead of different values. That would mean two indices instead of one. It also means about twice the storage space for the same information (assuming something like tinyint is used for the expense_type). You would also avoid the union clause, in case both types of expenses are needed. Commented Mar 18, 2014 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.