The Zen approach to designing a business system

Part 4: Use Cases/Business Scenarios

Julie, the owner of the bakery Cakes-&-Kisses (which makes yummy cakes for weddings and special occasions we discussed in our previous posts in this series) is all excited on making her business more efficient using Zoho Creator, the online cloud-based database and business application making software.

Have you thought about the problem with the ‘List of Orders’ entity / table we designed in our last blogpost? To recap, here’s what I asked. What’s wrong with this design of the ‘List of Orders’ entity?

Hmmm … there are several things not quite okay. But there is one thing which is quickly obvious. That is to do with Payments. As you would have guessed by now, one Order can have multiple Payments associated with it. For example, a Customer’s Order might be for $550. He may make the initial payment of, say, $100 in cash, then another $200 in bank transfer, then the rest when he comes to pick up the cake at the bakery. In this design of the Order entity, there is no way you can record three payments against one order. So, it’s fitting if we draw a separate ‘Payments Tracker’ table. So let’s do it!

Beautiful! So now we can track payments in detail.

But is this enough? No, not yet! We also need to maintain the status of payments in every order, so we know which orders are paid, which ones are pending and so on. But you might think that we can always track from the list of payments as to which ones are paid, which ones aren’t yet.

Yes, we could do that. For example, if a Customer’s Order Amount equals $320, then he might have paid $110 on 15th of last month and another $50 two days ago. So these will be two entries in ‘Payment Tracker’. Of course, you can add these two to get the total paid amount and then calculate that he has $160 pending in payment. But every time you need this detail (that is, say, to check whether there is any pending payment on a specific Order, or to see the list of pending payments) you need to go through the list of entries in ‘Payment Tracker’ and calculate the total repeatedly each time.

Instead, it is more convenient to keep the total pre-calculated so you can instantly refer to it. And if you have such pre-calculated values, then you can even pull out beautiful reports using Zoho Creator like this one:

Such reports are extremely powerful in giving you a quick snapshot of where you are with respect to customer payments, and lets you quickly pull out the list of those customers whose payments are not yet in.

Okay, coming back to our schema design. So here’s where the ‘Amount Paid So Far’ is being stored right now:

Yes, in the ‘List of Orders’ entity. Do you think that’s a great idea? Think about it for a moment. If you notice closely, you can see that the other columns (or ‘fields’ in Zoho Creator parlance) like ‘Order ID’, ‘Customer ID’, ‘Order Date’, ‘Due Date’ are all mostly fixed details -> that is, you enter them once when the Order comes in, and that’s it. But what about ‘Paid Amount’ and ‘Payment Mode’? These are values that could keep changing over time. In such scenarios, it is best, as we discussed earlier in part 2 of this series of posts, to separate payment related details into a separate entity like this:

So, here’s how our ER Diagram (if I may use that one geeky term! ‘ER’ stands for ‘entity-relationship’) looks like now:

Click here to download a higher resolution copy for your reference.

Before we end this post, here’s a question from Aunt Ann:

How do you pass an Order through various Departments in the Bakery? For example, a 7-tier wedding cake has to go through several Processes before it is ready for Delivery. Now how do you model this?

Clue: Think deeply about the words that start with a capital letter in the above paragraph!

This is a guest post by Priya.Sri, a Zoho expert. Passionate about the ways in which software and automation help achieve in the real world, and in making businesses more productive, she publishes articles about best practices for businesses. She has been designing and implementing custom business workflows/apps for small and medium businesses for four years, and brings with her a rich experience of about 15 years in the software industry. For more information, contact