Data Modeling Project / Quote / Order / Invoice

Question

I am currently working on a small project in which I need to simulate the following scenario:

Scenario

• The customer is calling, he needs a quote on the new car.
• Sales Representative. register customer information.
• Sales Representative. create a quote in the system and add the item to the quote (car).
• Sales Representative. Email the customer a quote.
• The client accepts the quote, and the quote is no longer a quote, but an order.
• Sales Representative. check the order, everything is in order, and he invoices the order. Now the order is no longer an order, but an invoice.

Thoughts

I need a little help finding the perfect way to simulate this, but I have some thoughts.

• I think both projects / quotes / invoices are basically an order.
• The draft / quote / invoice needs separate unique numbers (id), so I think there are separate tables for them.

Model

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

Concern

However, I have doubts about this model:

• The project / quote / invoice may have different positions and prices in the order lines. In this model, all draft / quote / invoice are connected to the same order as well as order lines, which makes it impossible to have separate lines of lines / lines of lines / invoices. Maybe I will create new tables for this, but then basically the same information will be stored in several tables, and this is also not good.
• Sometimes two or more quotes become texture, how will this model take care of this?

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

EDIT: Data Model v.1.4

Answer 1

It looks like you modeled each of these things - the quote, order, draft, invoice - as structurally identical to everyone else. In this case, you can "click" all similar attributes into one 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 this will work for the model you described, but I think that you will be better served in the end, modeling them as a supertype / subtype. Columns common to all subtypes are pushed up into a supertype; each subtype has a separate table for attributes unique to that subtype.

This SO question and its accepted answer (and comments) illustrate the supertype / subtype design for blog comments. Another issue relates to individuals and organizations. Another one regarding staffing and phone numbers.

Later.,.

It is not complete, but I do not have time. I know that it does not include positions. Perhaps they 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); 

Answer 2

There should be a “quotelines” table that looks like “ordinal lines”. Similarly, you must have an invoicelines table. All of these tables should have a “price” field (which will nominally be part of the default price) along with a “discount” field. You can also add a discount field to the quotation marks, orders, and invoices tables to handle things such as cash discounts or special offers. Despite what you write, It’s good to have separate tables, since the amount and price in the quote may not coincide with what the customer orders, and again it may not be the same amount that you actually provide.

I'm not sure what a draft table is - you could probably combine the draft and invoices tables, because they contain the same information, with one field containing the status of the project account or the final one. It is important to separate your account data from the order data, as you will presumably pay taxes in accordance with your income (accounts).

Quotations, Orders, and Invoices must have a field (foreign key) that contains the value of the sales representative; this field points to a nonexistent SalesRep table. You can also add the "salesrep" field in the "clients" table, which indicates the default reputation for the customer. This value will be copied to the quotation table, although it can be changed if another comment gave a quote by default. Similarly, this field should be copied when the order is made from a quote, and the invoice from the order.

I could add much more, but it all depends on how complex and detailed the system you want to make is. You may need to add a “specs” form if the cars are tuned to their options and priced accordingly.