I have a question relating to database design for an ecommerce. Almost everything works fine, but I need to handle backorder, which is the case when some products in an order from customer is currently out of stock, so these products will be put in a backorder.

“We have one guy working in shipping. He packs and sends the records if they are in stock in response to the customer order. The accountant still sends the invoice in a return e-mail when the records have been dispatched. Any items that were not in stock are placed on a back order list and these items are usually re-ordered from the preferred supplier. Occasionally items are ordered from alternative sources.” This is the detailed requirements for this task

I have some ideas about it, but still no satisfied. For example, I will add an atribute for table “record_order”( the table to store all set of products in an order ) called “is_backorder”. In case it is set as “false”, they will be shipped, else, a backorder will be order to the supplier, and when this backorder is shipped from supplier to the stock, it will be processed and shipped to customer. I know it is hard to understand, but that is the only solution I can think of. Please give me some guides or hints or ideas that you guys think can handle this case.