Take the 2-minute tour ×
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.

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?

share|improve this question
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 Mar 18 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.

share|improve this answer
    
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_col Mar 18 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 Rambeaud Mar 18 at 14:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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