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.
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:
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.