Data modelling draft/quote/order/invoice Data modelling draft/quote/order/invoice sql sql

Data modelling draft/quote/order/invoice

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_canceledfrom statement t1inner 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.