10

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.

Data model v.1.0 Concerns

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 model 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 enter image description here

Martin at Mennt
  • 5,677
  • 13
  • 61
  • 89
  • Columns common to all the subtypes should move up into the supertype. If every subtype has a subtype_date column, it should probably move into the supertype. (Drop quote_date, order_date, invo_date; just use stmt_date.) Same for subtype_canceled. The columns order_id and invo_id need to stay in their subtypes, because you need to account for every id number. (No gaps.) That might be true for quotes, too. I don't know. – Mike Sherrill 'Cat Recall' Apr 19 '11 at 00:32
  • OK, I understand. Think I misunderstood. The reason for why I added order_date in the subtype is that I wanted quote/order/invoice date to be different from each other, I also thought that statement was the same for all quotes/orders/invoices. But I see that for each quote/invoice/order you make, you also make one statement, right? But what happens when a quote is accepted, and need to be turned into an order? Do i just create a new order and copy the quote details, so that both the old quote and the new order exist? Would it be two statements too, one for each? Can I relate the Q to the I? – Martin at Mennt Apr 19 '11 at 06:41
  • If you build your tables this way, each row in "statement" is either a quote, an invoice, or an order. When a quote "turns into" an order, you insert a row into the updatable view "orders" (see my answer below), then copy the quote line items. (You only need one table of line items; I'll try to post that later.) If there are *never any changes* to line items--and that's a *hard* never--you can use a different, simpler structure. (I often see stuff like, "Yeah, we quoted $39.95 for that, but we appreciate your business, so we'll just throw it in for free" when it comes to invoicing.) – Mike Sherrill 'Cat Recall' Apr 19 '11 at 12:25
  • And, yes, you can relate the quote to the invoice. Just store the quote number (and, I suppose, the order number) in the invoice subtype. – Mike Sherrill 'Cat Recall' Apr 19 '11 at 12:26
  • So you think I should add a field for Quote-/OrderID in the Invoice table? What if there is no Quote or Order related, then the fields will be blank? Is that good DB design? – Martin at Mennt Apr 20 '11 at 17:41
  • If you can issue an invoice without having a quote or an order, I'd rather put information about the relationships between invoices, quotes, and orders in a separate table. I wouldn't even mind having two tables, one to relate quotes to orders, and another to relate quotes to invoices. (As long as that kind of structure fits the business requirements.) – Mike Sherrill 'Cat Recall' Apr 20 '11 at 18:25
  • I have posted my updated data model, could you please take a look and give me your comments? Only thing im missing in that model is how to track changes to quotes/orders/invoices. So that if the invoice was based on a quote, I want the ability to look up the original quote. – Martin at Mennt Apr 20 '11 at 20:37
  • You said "So that if the invoice was based on a quote, I want the ability to look up the original quote." I said, "I'd rather put information about the relationships between invoices, quotes, and orders in a separate table." – Mike Sherrill 'Cat Recall' Apr 22 '11 at 00:19
  • Yeah, then I need many tables, quote/order, quote/invoice, order/invoice. Is it not a better way to do this? Do I really need 3 tables? – Martin at Mennt Apr 22 '11 at 07:18
  • @Martin: I think you probably do need three tables to model how that part of the business works. Can you have quote -> invoice (no order)? Quote -> order (no invoice)? Order -> invoice (no quote). Multiple quotes -> one invoice? Multiple orders -> one invoice? Multiple quotes -> one order? – Mike Sherrill 'Cat Recall' Apr 22 '11 at 11:02
  • @Martin are you still satisfied with this data model? Also, for billing/shipping addresses, would you copy them from the customer to the statement or simply reference them from the statement? – Josh C. Sep 14 '15 at 01:45

3 Answers3

6

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.

No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • Thank you for your comments, I have updated my data model, could you please take a look at it now? – Martin at Mennt Apr 17 '11 at 11:48
  • 'Orders' should have a link to 'quotes', not the other way around. The sequence should be quotes -> orders -> invoices (I omit drafts, because I think it's unnecessary), where every table contains a link to the preceding table. – No'am Newman Apr 17 '11 at 12:12
6

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);
Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Yes, i started out like this but then I came a cross an issue. I need invoices/drafts/quotes to have separate numbers (ID's). Orders and invoices need to have a different number series than orders. – Martin at Mennt Apr 17 '11 at 11:53
  • It's just an integer; you can put any number you want in there. If you alter the primary key to make it (stmt_id, stmt_type), you can have independent numbering for each kind of statement. But the issue you raised is exactly the kind of issue that supertype/subtype design solves so well. Order number would go in the table that implements the subtype "order"; invoice number in the table that implements the subtype "invoice". – Mike Sherrill 'Cat Recall' Apr 17 '11 at 12:01
  • Hmm.. makes sense. I have updated my model, could you please take a look. – Martin at Mennt Apr 17 '11 at 12:59
  • @Martin: I think you need to read the questions and answers I linked to again. – Mike Sherrill 'Cat Recall' Apr 17 '11 at 21:27
  • @Catcall: I have read them, I got the insert part of it. If an quote is to be created, i add one row in statement- and quote table. This is fine, but what im not 100% sure of is when an quote become an order/invoice, what kind of operations need to be done? – Martin at Mennt Apr 17 '11 at 21:42
  • @Catcall: Thank you for your update, I have revised my data model and posted a pic. I have also attached the SQL drop of my tables. I had some problems adding FK between order/quote/invoice stmt_id/stmt_type and statement stmt_id/stmt_type. – Martin at Mennt Apr 18 '11 at 15:44
0

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.

Kad
  • 1
  • 2