I need to extend an accounts receivable module to support multiple
orders per invoice; our use case is a client that receives a constant
stream of orders from their customers, and would like to bulk a week
or so into one invoice.

There's currently, as far as I can tell, an 1:1:1 relation between
quotations, orders and invoices (all are optional). However, the
current schema is a little puzzling..
There is a single table, invoices, for all three, and it seems like
each entity got its own columns with very little or no overlap.
There's a varchar column for specifying the type.

Are there any advantages to this approach, or it as weird as it seems?
Considering there's no/little overlap between the types, I imagine all
three could be stored in a single row, but as far as I can tell
related entries do get separate rows.
Only advantage I see is that the line item table can be reused, but..

My ORM framework works fairly well with a superclass and its
subclasses stored in a single table, so if there's any point to the
schema, I could probably make it work; rename the table to
'abstract_order' or something, merge any 'common' fields, and use it
as a superclass for the other 3 in my domain layer..?

No matter what, I'll have to redo a lot of the application code, as
the domain layer is empty and the code is spread across controller &
view instead.
_____

Anyways, to accomplish my goal here I need a one-to-many relationship
between invoice and order. I figure it can't hurt to allow an order to
be split across multiple invoices either, i.e. many-to-many, even if
that isn't a requirement right now..?

Should an invoice make sense on its own, or is it safe to declare that
it always has an order? Deferring invoice line items to order sounds
pretty good..?

How about quotations? I'm not even sure how those should work in a
business sense. They do seem very similar to orders..


Any feedback appreciated,
Isak

All I can tell you is...
1. Collect orders, tasks or however we call it - in one table - let's
call it 'orders'.
2. When invoice is beeing done - scan table 'orders' and
2.1. - in single transaction -
2.2. - generate invoice - table 'invoices'
2.3. - generate invoice's position - table 'invoice_positions'.
Relations: invoices -< invoice_positions >- orders - so you get many-
to-many between invoices and orders.
Most information can be stored in orders table. It is not perfect
solution, but works

--
Regards,
Micha Zaborowski (TeXXaS)

On Oct 18, 10:54 am, Isak <isak.han... (AT) gmail (DOT) com> wrote:
For three entities, I would tend to make three tables. There will be
some more overhead in the lifecyle of items (quote move to orders move
to invoices), but your sanity will remain intact. Then it will be a
lot easier to create a batch-invoice entity that joins multiple
invoices for billing purposes. This last entity might be implemented
as a table or a view (might require a batch ID of some kind on invoice
table for the view to work.)

HTH,
Ed