Invoices and Bills in single table?

Question

If i have bills (from vendors), and invoices (to customers), would it be better to keep them separate (in two tables), or in a single table?

What about vendors / customers?

These are all relational-inheritance problems, can you give me some best practices regarding this?

Answer 1

In our ERP system we have bills and invoices separate; vendors and customer we have in one table.

Answer 2

I would suggest that if they are separate concepts, keep them separate. Usually when you start out designing a database and you have entities that have the same columns in their tables, the urge arises to consolidate them into 1 table. If you know for a fact that for the duration of the solution, you will never need ta add say customer-specific fields or vendor-specific fields, I would say go ahead and make them 1 table.

Keeping them separate allows each entity to develop on its own as the system grows and matures.

Answer 3

It seems to me that an incoming bill and an outgoing invoice are really different things:

• The outgoing invoice will have line items that match your product table
• The incoming bill will have a very formal approval process, involving managers, finance, and accountants
• The "From" on an incoming bill is your partner, but on an invoice, the partner's name is the "To"

Entities that are that different should have their own table. If they have common properties, you could store them in a Document table, and create a relation between that table and the Invoice and Bill table.

Answer 4

A dangerous road for many reasons.

I suppose logically speaking you could collapse everything into one table with a column "PrimaryKey" and optional columns Col1, Col2, Col3, etc. all varchars interpretable to other types.

Especially with financial data where there are audit trail and archiving issues, this is not a simplification.

(But it's the sort of strategy we all go through as a passing phase ...:) )