I am trying to forget about tables and think about objects, i am a bit confused about this i appreciate your clarification, this is about relations between orders and invoices:
In shopping cart user should be able to add multiple items but finally when order should be created, this is better to create separate record for each order item because some items may have recurring invoices and some items may not, or may be even some items are included for free like bonus etc. So to manage recurring invoices better to have separate order record for each item however all items were in one cart. But for these all order items there should be one initial invoice on first purchase (however for next recurring there might be separate invoice for each item) so at this time the relation from order to invoice at first time is many to one: Some order items have one invoice because purchased together at the same time and payment should be done once for all items. But on recurring invoices, the relation from order to invoice would be one to many, because order done once and has several recurring invoices, so thinking as object the relation between order to invoice is many to many? Right? I appreciate your advice.
First step might be to break out your main bushiness domain objects. I see:
ShoppingCart
ShoppingCartItem
Order
OrderItem
Invoice
InvoiceItem
…InitialInvoice
…ReccuringInvoice
Consider writing the name of each class on a 3x5 card and using it to help organize stuff.
Next, divide and conquer. ShoppingCart and ShoppingCartItem are pretty clear. In domain driven design terminology they would be known as a bounded context. As the user browses around they add items to their cart. No need to worry about checkout or orders or invoices at this point.
In a similar fashion, Order and OrderItem seem to go together. An OrderItem is similar to a ShoppingCartItem but will have different behavior within your order bounded context.
Then comes your invoice system and frankly your requirements were not at all clear to me. Clearly you have two kinds of invoices which somehow get tied to orders. But hopefully, by breaking things into there separate “bounded contexts” (ShoppingCart,Order,Invoice) thing will start to fall into place.
After finishing cart and going to checkout, a record for order should be created. Cinsider multiple products were in cart. Now for each product should be separate order record because of next recurring invoice, because a product may be one time and another one is monthly so if all of them are in one order record there will be a mistame in next recurring invouce? Right? So for each individual product regardless they are paid in one cart and one invoice, there should be a separate order created? If yes, these all orders must have one invoice because they are bought in one cart and one payment should be done so at this time the relation of order to invoice is many to one? Then on recurring invoices the relation of order to invoice is one to many? So actually the relation of order to invoice is many to many?
Association mapping is a bit complicated, but its actually not that bad. Usually association mapping is used for many-to-many relationships, although one to many can be done this way as well when needed. You have to think in object relations/associations, good examples will help you understand the concept better. Lets say you have a friends/contact system, you have a users table that stores users information, but where is the many-to-many relationship of friends going to be? You will need an association table, a table that maps friend-associations. This table should contain two columns, user1 and user2. When you search in this table, you know that a user is friend of another user if you can find such a record in the association table that user 1 maps to user 2 properly. The order may or may not matter, since friends can be unilateral or bilateral, but I hope you get the idea.
Thanks for nice explanation. But which asdociation structure do yku suggest for order to invoice as i dezcribed above?
Any advice yet?
Your requirements are unclear to me. Try writing a simple story. A customer comes to your site and want to buy one thing. Walk through the process and show what needs to be done then develop a model to support it. Then write more complicated stories.
I am asking association is good for the I said above? Many to many? One to many? Or?
I think it’s a language barrier. This makes no sense to me in English:
Now for each product should be separate order record because of next recurring invoice, because a product may be one time and another one is monthly so if all of them are in one order record there will be a mistame in next recurring invouce?
That is why you need some code or maybe a diagram.
Ok, i try to say what i mean to ask in an example:
A customer purchases 4 services at once. Service A must be paid right off. Service B is split in 3 payments billed once a month. Service C is a monthly subscription, billed until canceled. Service D is an yearly subscription, billed until canceled. How do I model the order and the invoices?
So you have an Order.
an Order has 1 or more Invoices
I guess that an Invoice has a reference to a Product.
For the “bill once a month, bill once, etc”, I would just use a property on the Invoice table. A boolean like “RecurringPayment”. If it’s false, it needs to be paid right away. If it’s true, it needs to check the “RecurringPaymentPeriodType” property, which contains “monthly, weekly, whatever…”. And the invoice could also has a “LastPaidDate” property, so your program knows when an invoice is due or not.
And how to model between product and order? Imagine we have 3 products, one is one time, another should be renewed monthly and 3rd product should be renewed yearly. But the buyer purchased all of these 3 products at once in a cart, so these all 3 products with different renewal cycle are in one order with one invoice. As about renewal problem you suggested to add a reference to product in invoice table that sounds reasonable. But now how to model between product and order?
Oops, sorry even if we have reference to product in invoice table, as i said 3 different products are purchased together in a cart so all of them has one invoice because payment is once for all products as they wrre in a cart together. So the reference to product in invoice table is still a problem as that invoice is the sum of 3 products! Please clarify.
I never worked on a system with recurring invoices. We paid everything all at once on the systems I worked on, so maybe I’m missing something here but here is what I would do…
Solution #1:
The invoice would not be related necessarily to exactly what was added to a cart.
You add 3 products with 3 different recurring period types, you have 3 different invoices. You could relate those invoices to a cart or an order or whatever you want to call it.
Now, you don’t want the customer to pay 3 different real invoices the same day, it should be all in one invoice if it’s the case.
The way I would do it is only with the “algorithm” that sends the invoices. It would take all the invoices due today, if there are more than 1 for the same customer, you send him only one “real invoice”. The way to handle this is maybe by adding another table/object like grouped invoices? When the user pays his “grouped invoice”, you mark the invoices as paid like you would if they were individually paid.
Solution #2
You create the next invoice each time an invoice is paid. So, you have an invoice that contains 3 products. When it’s paid, you check the invoice’s products and if there are products that must be paid in a recurring fashion, you create the new “next invoices” with its due date.
About your last question, you will need a table between invoice and products, like INVOICE_PRODUCT with the fields PRODUCT_ID and INVOICE_ID (and possibly PRICE). The primary key of the table should be both PRODUCT_ID with INVOICE_ID. So that’s a many to many relationship. That way, you can find out what products was bought for what invoice.
Now, you have to think carefully about “time”. What happens if the price of the product changes over time? Will the payment process still work? Is there a “show last invoices” screen for the user? That’s why I would probably copy the price in the relationship table. On system I worked on, all the product information was always copied into the invoices. If the product changes, is deleted, etc… the “invoice history” will still work and you’ll still be able to run reports.
As abour order-invoice modelling, I looked at WHMCS.
If understood correctly. I see even if we order multiple products, we will have only one order record with one invoice. In order table we have invoice_id coloumn, but in invoice table there is no reference to order, so this is one way from order to invoice. On time of ordering, there will be separate record in hosting table for each item with recurring cycle and end_date e.g. ‘Monthly’ , ‘01-05-2014’. this table has reference to order table, let’s name this table as ‘Order_Items’. when cron runs, I think it is reading cycle and due date from hosting table and create a new invoice. Only a new invoice will be created, nothing else will be re-created. this invoice has only reference to client, there is no reference to order table nor to hosting table nor vice versa. Did I understand correctly?
If yes, then question is that if invoice will be overdue, then how the system understand which hosting package should be suspended as there is no reference from invoice to order nor to hosting?
Please tell me if I am correct guessing how they did model invoice and order?
Also to @ahundiak and everyone else!
Please read post above first.
If I understood whmcs correctly, here is the scenario:
- user orders several items in a cart.
- when finished ordering, regardless how many items are purchased, only one Order and One invoice records will be added in the appropriate tables.
- in order we have a invoice_id field to map with invoice.
- mapping between Order to Invoice is OneToOne.
- 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.
- 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.
- 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.
Is this the scenario of invoice creation of WHMCS? 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?
PS. OrderItems above is called ‘hosting’ in whmcs.
OK, so you’re trying to understand how an existing system (WHMCS) database is structured and how it works? It should be part of you main question because I thought that you were trying to model one yourself, and there are probably millions of different ways to do that. I don’t know WHMCS, but I found out that they have a forum and it seems pretty active. Did you try to ask there?
I am not sure if i understood that whmcs modelling I sadid in prev post correctly. Can someone rxplain if i uderstood that modelling correctly?
Any advice yet?