The Database Schema

The Purchase Management application will use the existing WeMakeCars database. The Production department will maintain the tblRequisitions table. The system administrator too will maintain all master tables, such as tblItemMaster, tblSuppliers, and tblItemSupplierCostList. The Purchase Management application will manipulate the remaining tables. Figure 24.5 shows the database schema.

Figure 24.5: Schema for the WeMakeCars database.

The database schema includes these tables:

• tblItemMaster. Contains details such as item code and description of the item.

• tblSuppliers. Contains the supplier's details, such as the code and name of the supplier.

• tblItemSupplierCostList. Contains the cost details of the item supplied by a specific supplier.

• tblRequisitions. Contains details of the requisitions, such as the item code, the supplier code, the quantity required, the quantity ordered, and the date by which the requisition is required.

• tblPurchaseHeader. Contains details of the purchase order, such as the purchase order number, the generation date of the purchase order, and the supplier's code.

• tblPurchaseDetails. Contains details of items ordered in a purchase order, such as the purchase order number, the item code, the quantity ordered, the unit price of the item ordered, the date by which that order has to be made, and the quantity received.

• tblInvoiceHeader. Contains the invoice number, the invoice date, the supplier's code, and the invoice sent against a particular purchase order.

• tblInvoiceDetails. Contains the details of the invoice, such as the invoice number, the item code, the quantity supplied, and the unit price of that particular item.

Database Relationships

The Purchase Management application handles items and suppliers. This is clearly reflected in the database schema. Tables that contain the Item_Code field refer to it as a foreign key, while tblItemMaster has the primary key. In the same way, all the instances of Supplier_Code are referred to as foreign keys, while the primary key lies in tblSuppliers. The following are the relationships between these tables:

• tblItemMaster. This has a one-to-many relationship with tblItemSupplierCostList, tblRequisitions, tblPurchaseDetails, and tblInvoiceDetails.

• tblSuppliers. This has a one-to-many relationship with tblItemSupplierCostList, tblPurchaseHeader, and tblInvoiceHeader.

• tblPurchaseHeader. This has a one-to-many relationship with tblPurchaseDetails and tblInvoiceHeader.

• tblInvoiceHeader. This has a one-to-many relationship with tblInvoiceDetails.

Database Structure

The details of the items are stored in the tblItemMaster table. The structure of tblItemMaster is shown in Figure 24.6.

Figure 24.6: The design of the tblItemMaster table.

The tblItemMaster table contains Item_Code as the primary key column; its datatype is char. The other field in this table is Item_Description.

The details of the supplier are stored in the tblSupplier table. The structure is shown in Figure 24.7.

Figure 24.7: The design of the tblSupplier table.

The tblSupplier table contains Supplier_Code as the primary key column; the datatype of this key is char. The other field in this table is Supplier_Name.

The details of the costs of the items supplied by different suppliers are stored in the tblItemSupplierCostList table. The structure of this table is shown in Figure 24.8.

Figure 24.8: The design of the tblItemSupplierCostList table.

In this table, the primary keys are Item_Code and Supplier_Code. The datatype of both these keys is char. The other field in this table is Unit_Price.

The details of the material requisitions from the Production department are given in table tblRequisitions. The structure of the table is shown in Figure 24.9.

Figure 24.9: The design of the tblRequisitions table.

This table specifies the details of the requisition. The primary keys are Item_Code and Supplier_Code. The datatype of both the keys is char. The other fields included in the table are Qty_Required, Qty_Ordered, and RequiredByDate.

The header information related to a purchase order is stored in the tblPurchaseHeader table. The structure of tblPurchaseHeader is shown in Figure 24.10.

Figure 24.10: The design of the tblPurcbaseHeader table.

In the table tblPurchaseHeader, the primary key is PO_Number. The datatype of this key is integer autonumber. The other fields included in this table are Gen-Date, Supplier_Code, and Total_Amount.

The details of the items ordered in a purchase order are stored in the table tblPurchaseDetails, as shown in Figure 24.11.

Figure 24.11: The design of the tblPurchaseDetails table.

In the table tblPurchaseDetails, the primary keys are PO_Number and Item_Code. The datatype of PO_Number is integer autonumber, and the datatype of Item_Code is char. The other fields in the table are Qty_Ordered, Unit_Price, and DateRequiredBy.

The invoices received in the XML format are stored in the tblInvoiceHeader and tblInvoiceDetails tables. The common header information is stored in the tblInvoiceHeader table, as shown in Figure 24.12.

Figure 24.12: The design of the tblInvoiceHeader table.

In the table tblInvoiceHeader, the primary key is InvoiceNo. The datatype of this is char. The other fields in the table are InvoiceData, Supplier_Code, Total_Amount, and Against_PO.

The item-level details of an invoice are stored in tblInvoiceDetails, as shown in Figure 24.13.

Figure 24.13: The design of the tblInvoiceDetails table.

In the table tblInvoiceDetail, the primary keys are InvoiceNo and Item_Code. The datatype of both the keys is char. The other fields included in the table are Supplied_Qty and Unit_Price.