3

Below is the ERD of my current db schema for the orders part of an e-commerce site. The site is based on oscommerce, but has been modified, also I have removed quite a few of the fields from the tables to simplify the diagram.

current erd

I am planning to add the functionality to create quotes in the administration of the site. A quote will be very similar to an order, and most quotes will be converted to an order. There will be some different data needed for each, although most fields will appear in both, such as customer details. Also the original quote should be maintained so that it can be used again in the future, that is modifying an order that has been created from a quote does not modify the original quote.

Considering the similarities; it felt unnecessary to duplicate the whole of the orders structure for quotes, especially as the child tables for orders are in the format needed to store the data for quotes.

The approach I thought I might go for is to create a quotes table, that would be similar to orders and then reuse most of the orders child tables to work also with a quote.

(Probably I will still duplicate orders_status and orders_status_history to quotes_status and quotes_status_history as there will be different statuses for quotes, and a quote and order should only have a relevant status.)

Tables I was hoping to re-use for a quotes:

  • orders_total
  • admin_watch_list
  • orders_products
  • orders_products_attributes
  • orders_products_downloads

In order to re-use these tables, it would seem to me that I would have to do one of the following:

  • add a quotes_id field to each one of the tables listed above. Although then each of theses tables will have a 'quotes_id' and 'orders_id' one of which should always have a value and the other should always be NULL.
  • add an orders_id field to the quotes table, start the auto increment of the orders_id in my quotes table to say 1000000000 so that it doesn't interfere with the auto-increment orders_id that started from 0 in my orders table. The orders_id in quotes could then reference all the other tables using it's orders_id, although that orders_id would never appear in the orders table

Both of these seemed solutions did not seem ideal and I was wondering if there was a better way to accomplish what I wanted to do.

Ideally I would like to re-use as much as possible as it could mean faster development and less to maintain in the future.

I already have a page in my administration for editing orders, and I feel the closer I keep to the structure of orders, the easier I think it would be to reuse my code from edit orders page for a create/edit quote page which I need to add.

All help or advice is appreciated.

2

How about combine them? Keep the idea of an order table as the base, and add a status field with values:

  1. Entered - data being typed in, just started
  2. Quoted - quote provided to customer
  3. Ordered - customer has accepted the quote and the quote converted to an order

You mentioned keeping the original which is doable if you have an ORDERS_HISTORY or ORDERS_VERSION table. Basically, whenever the status of an order changes, update the main table but keep a snapshot of what it used to look like in the history table. This would let you pull up old versions of the quote/order to quote against.

  • 1
    Alternately, just maintain an flag indicating if a row is an order or a quote, and create a new order from a quote when needed. That would allow you to maintain distinct ID values for quotes (a history table as described would usually maintain the same ID value regardless of the current status). – RDFozz May 11 '17 at 17:34
  • Thanks for your help. I went with your advice of storing my quotes in the orders table with a flag. As @RDFozz suggested I used that flag to distinguish orders from quotes, so that I could maintain the original quote for re-quoting purposes. Cheers :) – dading84 May 14 '17 at 11:30
  • Europe only - may want to consider GDPR requirements – Joel Davey Jun 5 '18 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.