数据建模草图/报价/订单/发票 [英] Data modelling draft/quote/order/invoice

问题描述

目前，我正在开展一个小型项目，其中需要对以下情景进行建模：

场景

1. 客户来电，他想在新车上报价。


2. 销售代表注册客户信息。


3. 销售代表在系统中创建一个报价，并将一个项目添加到报价（汽车）。


4. 销售代表。


5. 客户接受报价，报价现在不再是报价，而是订单。


6. 销售代表检查订单，一切正常，他发票订单。订单现在不再是订单，而是发票。

想法

我需要一些帮助，找出理想的模式，但我有一些想法。

<我想，草稿/报价/发票基本上是一个订单。

1. 草稿/报价/发票需要单独的唯一编号（id），所以我正在考虑单独的表

模型

这是我的数据模型v.1.0，请让我知道你的想法。

关注

然而，我对此模型有一些担忧：

1. 草稿/报价/发票在订单行中可能有不同的商品和价格。在此模型中，所有草稿/报价单/发票都连接到相同的订单和订单行，使得不可能有单独的报价行/起草线/发票行。也许我会为此创建新的表，但是基本上相同的信息将被存储在多个表中，这也不是很好。


2. 有时两个或更多的引号成为发票，如何这个模型会照顾这个吗？

如果你有更好的建模模式的任何提示，请让我知道！ p>

编辑：数据模型v.1.4

解决方案

看起来你已经模仿了每一件事情 - 报价，订单，草稿，发票 - 与其他结构相同。如果是这样，那么你可以将所有类似的属性推到单个表中。

  create table statement（
 stmt_id整数主键，
 stmt_type char（1）not null检查（（d'，'q'，'o'，'i'）中的stmt_type），
 stmt_date date not null default current_date，
 customer_id integer not null  - 引用customer（customer_id）
）; 
 
 create table statement_line_items（
 stmt_id integer not null references statement（stmt_id），
 line_item_number integer not null，
  - 订单项的其他列
主键（stmt_id，line_item_number）
）; 
  

我认为这将适用于您描述的模型，但我认为您会更好从长远来看，通过将它们建模为超类型/子类型。所有子类型共同的列被推入超型;每个子类型都有一个单独的表，用于该子类型唯一的属性。

这个SO问题及其接受的答案（和评论）说明了博客评论的超类型/子类型设计。 另一个问题涉及个人和组织。 另一个与员工和电话号码有关。

之后。 。 。

这不完整，但我没有时间。我知道它不包括订单项。可能错过了别的东西。

   - 超类型。评论显示在他们适用的列之上。 
创建表语句（
  - 自动增量或串行在这里确定
 stmt_id整数主键，
 stmt_type char（1）唯一检查（stmt_type in（'d' q'，'o'，'i'）），
  - 保证只有order_st表可以引用具有
  -  stmt_type ='o'的行，只有invoice_st表可以引用具有
  -  stmt_type ='i'等
 unique（stmt_id，stmt_type），
 stmt_date date not null default current_date，
 cust_id integer not null  - 引用客户（cust_id） 
）; 
 
  - 订单子类型
创建表order_st（
 stmt_id整数主键，
 stmt_type char（1）not null默认'o'check（stmt_type = 'o'），
  - 保证此行引用表statement中具有stmt_type ='o'
  - 的行。
 unique（stmt_id，stmt_type），
  - 不要级联删除，甚至不允许删除，每个订单
  - 一个订单号必须保持责任，如果不是
  - 会计
外键（stmt_id，stmt_type）引用语句（stmt_id，stmt_type）
删除限制，
  - 自动增量或串行是*不*好的，因为他们可以有差距
  - 数据库必须计算每个订单号码
 order_num integer not null，
 is_canceled boolean not null 
 default FALSE 
）; 
 
  - 编写触发器，规则，无论什么使此视图可更新。 
  - 每个子类型构建一个视图，加入超类型和子类型。 
  - 应用程序代码使用可更新视图，而不是基表。 
创建视图订单
选择t1.stmt_id，t1.stmt_type，t1.stmt_date，t1.cust_id，
 t2.order_num，t2.is_canceled 
从语句t1 
 inner join order_st t2 on（t1.stmt_id = t2.stmt_id）; 
  

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.

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

解决方案

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);

这篇关于数据建模草图/报价/订单/发票的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！