Database Environment
Stephen K.D. Sylvester
DBM/380
15 October 2014
Gary Teed

Today I will be taking to you about the database environment, the term database environment refers to a type of configuration used to create and populate an application. The most commonly used definitions for databases, are items, which include data, which are text, images, graphics and facts associated to something.
A database is a collection of well-organized data, the environment that is to be designed is one that stores and retrieves data that easily maintained and organized. For the purpose of this assignment of the database environment, I have chosen will be a series of tables that store customer information, purchase orders, sales orders and various status point information. The data will be used for a series of reports that will show the status of the purchase orders as it goes through the database system. The goal of this system is to provide an accountable and accurate status of the items purchased that have been logged in the system. Over the last decade our company has had a hard time and struggled with lost records and documentation of purchase orders, with limited visibility on the status of those orders. W have submitted a request to create a well working database for our customer service to access, and be able to quickly and accurately find the status of that purchase order. Also we would like to implement a list of potential orders that may have dropped off, and gone missing and not have been processed. This will provide a metrics for order processing times and to set strides in helping our company. The system will consist of seven tables within the database environment, those seven include customer data, source system data, corrections data tables, workflow, drop ship corrections, comparison results, and end system data. Customer data table will be comprised of columns for customer numbers and names. The source system will be made up of columns for customer purchases for order numbers, names of customers, status and transmitted data. Corrections data tables will be comprised of columns for our customers purchase orders, date received, and any error messages. Drop ship data tables will be comprised of some data from the corrections section table such as purchase orders and error messages. The workflow sections will contain order number and status of those orders. The end system table will be comprised of sales orders, ordering dates, order status, and order numbers.
Finally the comparison table will be the output results of comparing all the tables together against the source system table to end with a complied list of all orders by date range with each status point.
Since customer purchase order number is the common join point from each table it will be the primary connection point. Some of the data contains additional data in the same field as the purchase order number so it will be required to have logic that addresses this to prevent duplication of rows. The data format will be text or numeric based on the type of data to ensure joins work properly. This database will include macros allowing user to import all the data easily without having to key each record manually, as well as allow for manual entry of data. It will also contain macros that complete the necessary reports so the users can perform one click reporting where possible. This will ensure consistency in the output data. The data as it is imported will be appended to the primary tables instead of creating new ones each time so that over time there is a complete database going back far enough to ensure users have enough data to provide status on any purchase order received electronically.
The initial scope of the database is orders placed by EDI or electronic data interchange between customers and supplier. The long-term goal is to also include outbound invoice and advance shipping notice data to be able to do same comparisons by customer or date range with status of those documents as well. The benefit of this database is to provide better control over our data management and be able to provide quicker responses to our customers when inquiring about inbound purchase order documents. Today the data is disjointed and not available in one location making it hard to track the full status of a specific purchase order.
In conclusion the database to be used by customer service and information system support personnel to provide status reports to customers on electronic orders. Having the reports for any orders that have not created a sales order with one click response will allow customer service to be more proactive on potential lost order data. Having this information before the customer calls to get the order into the system and provide fulfillment updates when the customer’s call will improve the relationship between supplier and customer.

References:
The Database Environment Retrieved Oct 15, 2014 from http://www.omputing.surrey.ac.uk/personal/st/A.Browne/CS263/Lecture1Notes.PPT
Managing customer orders with a database Retrieved Oct 15, 2014 from http://office.microsoft.com/en-us/access-help/managing-customer-orders-in-access-HA001034557.aspx
Work order template for database Retrieved Oct 15, 2014 from http://www.blueclaw-db.com/work_order_software_db.htm