Data modelling draft/quote/order/invoice

Im currently working on a small project in which I need to model the following scenario:

Scenario

1. Customer calls, he want an quote on a new car.
2. Sales rep. register customer information.
3. Sales rep. create a quote in the system, and add a item to the quote (the car).
4. Sales rep. send the quote to the customer on email.
5. Customer accept the quote, and the quote is now not longer a quote but an order.
6. Sales rep. check the order, everything is OK and he invoice the order. The order is now not longer an order, but an invoice.

Thoughts

I need a bit of help finding out the ideal way to model this, but I have some thoughts.

1. I'm thinking that both draft/quote/invoice is basically an order.
2. Draft/quote/invoice need seperate unique numbers(id's) so there for i'm thinking separate tables for all of them.

Model

This is my data model v.1.0, please let me know what you think.

I however have som concerns regarding this model:

1. Draft/quote/invoice might have different items and prices on the order lines. In this model all draft/quote/invoice is connected to the same order and also order lines, making it impossible to have separate quote lines/draft lines/invoice lines. Maybe I shall make new tables for this, but then basically the same information would be stored in multiple tables, and that is not good either.
2. Sometimes two or more quotes become an invoice, how would this mod开发者_运维技巧el take care of this?

If you have any tips on how to model this better, please let me know!

EDIT: Data model v.1.4

It looks like you've modeled every one of these things--quote, order, draft, invoice--as structurally identical to all the others. If that's the case, then you can "push" all the similar attributes up into a single table.

create table statement (
    stmt_id integer primary key,
    stmt_type char(1) not null check (stmt_type in ('d', 'q', 'o', 'i')),
    stmt_date date not null default current_date,
    customer_id integer not null  -- references customer (customer_id)
);

create table statement_line_items (
    stmt_id integer not null references statement (stmt_id),
    line_item_number integer not null,
    -- other columns for line items
    primary key (stmt_id, line_item_number)
);

I think that will work for the model you've described, but I think you'll be better served in the long run by modeling these as a supertype/subtype. Columns common to all subtypes get pushed "up" into the supertype; each subtype has a separate table for the attributes unique to that subtype.

This SO question and its accepted answer (and comments) illustrate a supertype/subtype design for blog comments. Another question relates to individuals and organizations. Yet another relating to staffing and phone numbers.

Later . . .

This isn't complete, but I'm out of time. I know it doesn't include line items. Might have missed something else.

-- "Supertype". Comments appear above the column they apply to.
create table statement (
  -- Autoincrement or serial is ok here.
  stmt_id integer primary key,    
  stmt_type char(1) unique check (stmt_type in ('d','q','o','i')),
  -- Guarantees that only the order_st table can reference rows having
  -- stmt_type = 'o', only the invoice_st table can reference rows having
  -- stmt_type = 'i', etc.
  unique (stmt_id, stmt_type),
  stmt_date date not null default current_date,
  cust_id integer not null -- references customers (cust_id)
);

-- order "subtype"
create table order_st (
  stmt_id integer primary key,
  stmt_type char(1) not null default 'o' check (stmt_type = 'o'),
  -- Guarantees that this row references a row having stmt_type = 'o'
  -- in the table "statement".
  unique (stmt_id, stmt_type),
  -- Don't cascade deletes. Don't even allow deletes. Every order given
  -- an order number must be maintained for accountability, if not for
  -- accounting. 
  foreign key (stmt_id, stmt_type) references statement (stmt_id, stmt_type) 
    on delete restrict,
  -- Autoincrement or serial is *not* ok here, because they can have gaps. 
  -- Database must account for each order number.
  order_num integer not null,  
  is_canceled boolean not null 
    default FALSE
);

-- Write triggers, rules, whatever to make this view updatable.
-- You build one view per subtype, joining the supertype and the subtype.
-- Application code uses the updatable views, not the base tables.    
create view orders as 
select t1.stmt_id, t1.stmt_type, t1.stmt_date, t1.cust_id,
       t2.order_num, t2.is_canceled
from statement t1
inner join order_st t2 on (t1.stmt_id = t2.stmt_id);

There should be a table "quotelines", which would be similar to "orderlines". Similarly, you should have an 'invoicelines' table. All these tables should have a 'price' field (which nominally will be the part's default price) along with a 'discount' field. You could also add a 'discount' field to the 'quotes', 'orders' and 'invoices' tables, to handle things like cash discounts or special offers. Despite what you write, it is good to have separate tables, as the amount and price in the quote may not match what the customer actually orders, and again it may not be the same amount that you actually supply.

I'm not sure what the 'draft' table is - you could probably combine the 'draft' and 'invoices' tables as they hold the same information, with one field containing the status of the invoice - draft or final. It is important to separate your invoice data from order data, as presumably you will be paying taxes according to your income (invoices).

'Quotes', 'Orders' and 'Invoices' should all have a field (foreign key) which holds the value of the sales rep; this field would point to the non-existent 'SalesRep' table. You could also add a 'salesrep' field in the 'customers' table, which points to the default rep for the customer. This value would be copied into the 'quotes' table, although it could be changed if a different rep to the default gave the quote. Similarly, this field should be copied when an order is made from a quote, and an invoice from an order.

I could probably add much more, but it all depends on how complex and detailed a system you want to make. You might need to add some form of 'bill of materials' if the cars are configured according to their options and priced accordingly.

Add a new column to line_items ( ex:Status as smallint)

When a quote_line becomes an order_line then set bit you choose from 0 to 3 to 1.

But when qty changes then add a new line with new qte and keep last line unchanged.

Kad.