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
February 21st. 2011