Geek asks and answers

Database design regarding my business logic

I am creating a billing application consisting of the following business logic.

a) Place a new order for the customer. (an order is a group of three related components, valuation, invoice and purchase)

b) After placing an order, a new valuation may be generated. An order will have only one evaluation (evaluation consists of details)

c) Regarding the assessment of order. an invoice can be generated. invoice at a discount. In addition to the product details, the invoice consists of some costs. An order can contain only one invoice

d) With reference to the order invoice, a PurchaseOrder may be generated. PurchaseOrder consists of product purchase information. An order may contain multiple PurchaseOrder.

here is the database table design that I came up with.

enter image description here

as long as everything looks good, I can hardly decide where to keep a list of items belonging to a particular evaluation, invoice or order order.

I thought of a few solutions.

A: . (, - )
: estimate_item, invoice_item, purchaseorder_item ( , order_item ).
: , , , - , .

B: order_item
tablename: order_item
: , , . .

1) column reference column: ( : , -, ) columnKey: type_id (consist of foreignKey )
: , , , tablename_id, . .

2) foreignKeyColumn: order_id, estimate_id, invoice_id, purchaseorder_id.
: .

, order_item, /-/, .

:

id

table name: order relates to (contact, estimate, invoice, shipment) tables.
column name: contact_id (foreign key(referring to id column of the contact table)).
column name: estimate_id (foreign key(referring to id column of the estimate table)).
column name: invoice_id (foreign key(referring to id column of the invoice table)).
column name: shipment_id (foreign key(referring to id column of the shipment table)).

tablename: purchaseorder (this have one to many relationship with order table)
column name: order_id (foreign key(referring to id column of the order table)).
column name: contact_id (foreign key(referring to id column of the contact table)).

, order_item.

.

1:

, estimate, invoice purchaseorder .

+3
2

, , . , "", " ", , ( ). "" "", ? , .

, "id" - , , , . , " ". .

, , . " " " " (, , FK), . .

+1

, , (.. estimate, purchaseorder / invoice), .

, , , Documents :

CREATE TABLE Documents (
  DocumentId   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  OrderId      INT NOT NULL,
  -- you can move any fields common to all document types here
  -- e.g. date created, reference #, etc.
  FOREIGN KEY (OrderId) REFERENCES order (id)
);

order_item, estimate, purchaseorder invoice :

ALTER TABLE [tablename]
  ADD COLUMN DocumentId INT NOT NULL,
  ADD FOREIGN KEY (DocumentId) REFERENCES Documents (DocumentId)
);

, ?

0

More articles:


All Articles