How to design database that handle Order, OrderItem, Return, Refund, Exchange?

Question

Most questions on internet focus on Order, OrderItem. There're very few question about designing a database that comprehensively handle all aspects of online retail (Order, OrderItem, Return, Refund, Exchange).

I just know basically this data model.

Product (ProductID, Name, etc)
Order (OrderID, Date, totalcost, etc)
OrderItem (OrderID, ProductID, Quantity, UnitPrice, etc)

Based on the above structure, how can i manage the Return, Refund, Exchange?

I noticed that when i return/exchange an item on the super market, the staff there regenerates a new Invoice. Is this the way they handle Return, Refund, Exchange?

Answer 1

• (F table.column) means a foreign key pointing to table.column
• (P) means primary key
• (U) means unique key

Here are some tables and example data...

addresses
    id          unsigned int(P)
    line1       varchar(50)
    line2       varchar(50) // Allow NULL
    city_id     unsigned int(F cities.id)
    zip         varchar(6) // 5 digits for US and MX, 6 characters (X9X9X9) for CA
    zip4        char(4) // Allow NULL

+----+-----------------+-------+---------+--------+------+
| id | line1           | line2 | city_id | zip    | zip4 |
+----+-----------------+-------+---------+--------+------+
|  1 | 123 Main Street | Apt A |      17 | 92101  | 1234 |
|  2 | 345 East Street | NULL  |      25 | T1X0L3 | NULL |
| .. | ............... | ..... | ....... | ...... | .... |
+----+-----------------+-------+---------+--------+------+

cities
    id                  unsigned int(P)
    state_id            unsigned int(F states.id)
    name                varchar(50)
    ...

+----+----------+-----------+-----+
| id | state_id | name      | ... |
+----+----------+-----------+-----+
| .. | ........ | ......... | ... |
| 17 |      130 | San Diego | ... |
| .. | ........ | ......... | ... |
| 25 |       14 | Calgary   | ... |
| .. | ........ | ......... | ... |
+----+----------+-----------+-----+

See ISO 3166-1

countries
    id              char(2)(P)
    iso3            char(3)(U)
    iso_num         char(3)(U)
    name            varchar(45)(U)

+----+------+---------+---------------+
| id | iso3 | iso_num | name          |
+----+------+---------+---------------+
| .. | .... | ....... | ............. |
| CA | CAN  | 124     | Canada        |
| .. | .... | ....... | ............. |
| MX | MEX  | 484     | Mexico        |
| .. | .... | ....... | ............. |
| US | USA  | 840     | United States |
| .. | .... | ....... | ............. |
+----+------+---------+---------------+

See PHP's crypt() function for hashing the password.

customers
    id              unsigned int(P)
    first_name      varchar(50)
    middle_name     varchar(50) // Allow NULL
    last_name       varchar(50)
    email           varchar(255)
    username        varchar(32)
    password        varbinary(255) // hashed
    ...

+----+------------+-------------+-----------+----------------------------+-----------+----------+-----+
| id | first_name | middle_name | last_name | email                      | username  | password | ... |
+----+------------+-------------+-----------+----------------------------+-----------+----------+-----+
|  1 | John       | Quincy      | Public    | [email protected]            | johnqball | xxxxxxxx | ... |
|  2 | Jane       | NULL        | Doe       | [email protected] | janeykins | xxxxxxxx | ... |
| .. | .......... | ........... | ......... | .......................... | ......... | .......  | ... |
+----+------------+-------------+-----------+----------------------------+-----------+----------+-----+

customers_addresses
    id              unsigned int(P)
    customer_id     unsigned int(F customers.id)
    address_id      unsigned int(F addresses.id)

orders
    id                  unsigned int(P)
    customer_id         unsigned int(F customers.id)
    bill_address_id     unsigned int(F addresses.id)
    ship_address_id     unsigned int(F addresses.id)
    created             datetime
    shipped             datetime
    ...

+----+-------------+-----------------+-----------------+---------------------+---------------------+-----+
| id | customer_id | bill_address_id | ship_address_id | created             | shipped             | ... |
+----+-------------+-----------------+-----------------+---------------------+---------------------+-----+
|  1 | 1           | 1               | 1               | 2012-12-31 23:59:59 | 2013-01-01 00:00:00 | ... |
+----+-------------+-----------------+-----------------+---------------------+---------------------+-----+

orders_products
    id              unsigned int(P)
    order_id        unsigned int(F orders.id)
    product_id      unsigned int(F products.id)
    quantity        unsigned int
    unit_price      double
    ...

+----+----------+------------+----------+------------+-----+
| id | order_id | product_id | quantity | unit_price | ... |
+----+----------+------------+----------+------------+-----+
|  1 | 1        | 1          | 1        | 12.34      | ... |
|  2 | 1        | 2          | 13       | 1.78       | ... |
| .. | ........ | .......... | ........ | .......... | ... |
+----+----------+------------+----------+------------+-----+

products
    id                  unsigned int(P)
    name                varchar(50)
    price               double
    ...

+----+----------+-------+-----+
| id | name     | price | ... |
+----+----------+-------+-----+
|  1 | Widget 1 | 12.34 | ... |
|  2 | Widget 2 | 1.78  | ... |
| .. | ........ | ..... | ... |
+----+----------+-------+-----+

returns
    id                      unsigned int(P)
    order_product_id        unsigned int(F orders_products.id)
    quantity                unsigned int
    ...

+----+------------------+----------+-----+
| id | order_product_id | quantity | ... |
+----+------------------+----------+-----+
|  1 | 1                | 1        | ... |
| .. | ................ | ........ | ... |
+----+------------------+----------+-----+

See ISO 3166-2

states
    id              unsigned int(P)
    country_id      char(2)(F countries.id)
    code            char(2) // AB, AL, NL, etc.
    name            varchar(50) // Alberta, Alabama, Nuevo Leon, etc.
    ...

+-----+------------+------+------------+-----+
| id  | country_id | code | name       | ... |
+-----+------------+------+------------+-----+
| ... | .......... | .... | .......... | ... |
|  14 | CA         | AB   | Alberta    | ... |
| ... | .......... | .... | .......... | ... |
|  72 | MX         | CH   | Chiapas    | ... |
| ... | .......... | .... | .......... | ... |
| 130 | US         | CA   | California | ... |
| ... | .......... | .... | .......... | ... |
+-----+------------+------+------------+-----+

Returns, refunds and exchanges are all really a return - the customer is returning a Product. How you handle it depends on your business rules...

Answer 2

you could build a table that handles returns and exchanges, something like

Returns (ID, OrderID, ExchangeID)

So if a customer returns something, you put the OrderID into Returns and you're done - and if they exchange something you process the new order, then put the returned item's OrderID into the Returns.OrderID field, and the new OrderID in the Returns.ExchangeID field, this way you know which product was exchanged for what. This should be flexible enough to allow for unlimited returns and exchanges too.

Obviously there's more to it than that - just a thought off the top of my head to get the ball rolling...

Answer 3

At risk of confusing things, you need another concept which is the lifecycle of an order item. Right now you have implicitly a "paid" lifecycle for an order-item. Each item actually has other lifecycle associated with it such as "ordered" (for example in a an online shop), "in stock", "packaged", "dispatched", "delivered" and so on.

After a sale you have additional lifecycle statuses.

The easiest way to do this is to have a lookup table with an entry for each lifecycle status, lets call it LifecycleStatus, and create a foreign key column to that from your OrderItem table. This may not, however, give you enough information, and loses any sort of history.

The next step is to add a LifecycleInfo table which foreign keys to your OrderItem table. It will also have a column to foreign key to the LifecycleStatus table. This table (which is a relation table as it happens, and many-to-many) should also have additional columns, usually this is at least a date and a string to hold something descriptive.

These two entities allow a single OrderItem row to have multiple statuses, and to track that status with some useful information.

The logic to handle these entries is usually pretty simple e.g. to prevent multiple refunds on the same row.

Hope that helps.