Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
–
AakashMMar 18 '14 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).

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.
–
wobbily_colMar 18 '14 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.
–
Sascha RambeaudMar 18 '14 at 14:24