Whats the best breakdown of order status types for shopping cart 'Order' TABLE ?

Question

I've seen several different shopping cart schemas with different tables for order status type / shipping status type / payment status type.

I want to get this right first time for my project and wondered what the best approach is, and hopefully someone with sample tables for me to use.

The key thing, of course is that however many columns I use - they must represent mutually exclusive things.

I'm thinking something along the lines of :

OrderStatus - Summary status PaymentStatus - Paid/Unpaid/PartiallyPaid/Error ShippingStatus - Unshipped/PartiallyShipped/Shipped/DeliveredByHand

whats the best way to break this down - should I have a 'summary' status too representing the overall 'human-readable' status as well as individual statuses for each independent part of the process?

Answer 1

This really, really depends on the full functionality of the cart itself. I would suggest following the SDLC which would give you a better idea what functionality you would need to begin with, leading to a clearer picture of what data (tables/fields) you would need to store in the database.

Here are some links to get you started with that:

http://en.wikipedia.org/wiki/Systems%5FDevelopment%5FLife%5FCycle

http://www.computerworld.com/s/article/71151/System%5FDevelopment%5FLife%5FCycle

Once you have that started, you can usually determine what fields and values you will need as you progress.

Once you get to the point of determining what data needs stored in your database, you can use database normalization guidelines to assist in structuring your tables

http://en.wikipedia.org/wiki/Database%5Fnormalization

Hope that helps!

Answer 2

Any time you have various states that are "mutually exclusive", it implies having a single column with multiple possible values for that column. Most of the time these values should be constrained, and one of the best and most common ways to do this is via a Foreign Key to a "dictionary" or "lookup" table. So, at it's most basic, you might have something like this:

• Table Order (OrderID, OrderStatusID, ...)
• Table OrderStatus (OrderStatusID, Name)

OrderStatus will have values such as: * 1, "Paid" * 2, "Unpaid" * 3, "Shipped" * 4, "Unshipped"

The important part is to determine which statuses are really mutually exclusive to other statuses. For instance, my example rows above probably aren't very good, as you could potentially have an order that is both "Paid" and "Shipped". If that was the case, then you might split OrderStatus into PaymentStatus and ShippingStatus (as you alluded to).

Determining whether or not to split these rows is really up to you and your specific needs. However, whatever you decide, assume that you will have to change it at some point. Normally, the only applications/databases that never change are the failed ones that are abandoned for lack of use. "Getting it right the first time" is an admirable goal, and doing your research ahead of time is warranted, but you'll almost certainly not achieve it. Instead, spend your effort on making the rest of your design/code flexible & changeable enough that you can rework parts of it without having to tear up the entire application.