modelling order and invoice

I am trying to model order and invoice. I was thinking of this:

1. user orders several items in a cart.
2. when finished ordering, regardless how many items are purchased, only one Order and One invoice records will be added in the appropriate tables.
3. in order we have a invoice_id field to map with invoice.
4. mapping between Order to Invoice is OneToOne.
5. upon ordering, when a new order record and a new invoice record are added, new records will be added in OrderItems, a record for each item on cart, so mapping between Order and OrderItems is OneToMany.
6. When cron runs, it checks the Due Date and billing cycle of items in OrderItems and generate a new invoice for each item as appropriate. There is no mapping from Invoice to Order nor to OrderItems. As it cannot be mapped to Order and OrderItems at the same time, but there is OneToOne mapping from Order to Invoice.
7. When cron runs, only a new invoice will be generated as explained above in #6, and NO new Order nor new OrderItem will be created.

Sounds reasonable? If yes, then if an invoice is overdue, how the system will know which hosting account should be suspended as there is no association from Invoice to OrderItems?

 

Sounds kosher to me.... that's pretty much how relational databases are supposed to work... Though I probably wouldn't have a separate 'invoice' table from 'order'. If the relationship is 1:1, don't waste time using two separate tables. Only make separate tables when the relationship is 1:many -- well, unless you aren't indexing properly and/or have massive amounts of data in each.... like doing something silly such as putting the full customer info in; which IS something that I'd have separate is a CUSTOMER table since once customer may have many orders; another 1:many relationship.

customer : orders : orderItems : products

Would be the relationship order I'd have -- that way orderItems just needs for fields "id, orderId, quantity, product, costPerUnit" -- I'd keep costPerUnit separate to the orderItem since the price per unit in products might change. Likewise "orders" would simply have "id, customerId, orderDate"

A separate "payments" table might be a good idea, someone might make many sub-payments for one order, multiple payment sources, etc, etc... Or was that what you were thinking of with 'invoices' being separate?

 

I agree with @deathshadow, apart from the part with separate costPerUnit - it might be wise to have that in the actual order-table, since it might change after the customer orders, and then you might want the customer to pay what the price was on the order-date (so the costPerUnit might change, but that doesn't change the customer's invoice). If I misunderstood you, @deathshadow, I apologize

 

You did, I was saying that for the orderItems table, which would be the items for each order... We're saying the same thing.