Database Answers Tutorial on ERD (Click for Web Site)
Home Ask a Question Best Practice Careers Contact Us Data Models Search Site Map
Data Model for Purchase Orders 
Back to the Data Model
A. The requirement is to design a Database for Purchase Orders

Subject: Problems creating database scheme/structure for Construction Purchase Order Management System
Question: I was wondering if someone could lead me in the right direction creating a 
database structure for the web app that I am trying to create for a construction company's ordering system. 

Overview: Projects will need to be created. 
All projects will have a Customer and Vendors who will have Primary Contacts assigned to them (ex. CustomerContacts, VendorContacts). 

Materials will be purchased for each project. 
Materials are purchased from different vendors. 

Vendors fall into different categories (hardware, lumber, etc..)Materials also fall into these categories. 
Vendors can supply more then one type of material, so they can fall under more then one category. 

Quotes will need to be created (this will be so the vendor can login to their vendor account and price the 
materials that they are asked to supply for a specific project. 

At this point there must be a way for the company (owner of database app) to login view each MaterialCategory 
which will have all of the quotes the vendors were asked to submit in a big comparison chart per category. 

Once the Company selects what vendors will be supplying each category. 
Purchase orders will need to be created. 
Then finally Invoices. 
I understand a system like this can be very complex, and I am probably leaving out many tables, but I just 
wanted to give you the basic idea of what the web app will do. 

I tried to map this out on my own and came up with this: Employees - EmployeeAddresses Employee Roles Projects 
Customers - CustomerAddresses Vendors VendorCategories Materials PurchaseOrders - PurchaseOrderAgreement 
Addresses Contacts ProjectVendorMaterialQuotes ProjectVendorInvoices ProjectVendorMaterialComparisons Everything 
will most likely be connected to PROJECT in the end. 

The confusion comes in because I don't know if tables that combine things like this with many foreign keys are 
acceptable (ProjectVendorMaterialComparisons). 

Barry Williams
February 21st. 2011
Principal Consultant
Database Answers


Home Ask a Question Best Practice Careers Contact Us Data Models Search Site Map