Introduction
The Precision Electronic Parts (PEP) is a small private entity with an escalated demand for the low voltage motors for the community healthcare centers. However, the existing billing and invoicing system do not support the increased need for the product. The phenomenon has resulted in an elevated backlog while processing client charging, invoices, and remittance receipts. With the support of the consultancy company, the IT (information technology) Steering committee has adopted and executed a new IT solution system that reduces the strain experienced among various departments, including accounts receivable, sales, and invoicing divisions. Some key issues, including processing defects, late billing, invoicing, and payments collection, will be reduced as the modern system will embed particular outputs, processing, and inputs software to reinforce automatic calculations. This document highlights the functional requirements to ensure that the given considerations and the design objectives offer a correct synopsis of the system architecture. In addition, it substantially explains the data blueprint related to the system. The system design specification will involve the description of the output and input layout and explaining the entity-relationship diagrams (ERDs) showcasing the association between the inputs and output data elements.
Output Layout
Even though PEP operates a monthly reporting plan embedded under each department, the primary aspiration includes keeping them informed regarding the maintenance of invoices and gathering client payments. To a greater extent, these functional prerequisites for the modern system will concisely elaborate on the main capability of the proposed solution, including the database retrieved from the system, information input to the system, and the processing of the data.
Output Layout 1 (Monthly Invoicing Report)
This report will substantially showcase the existing standing of the marketing department since they manage the commodity codes and pricing utilized by the invoicing database on the 15th of every calendar month. However, the monthly invoice report will be created for the client’s use instead of the PEP company. It will give a detailed printing of an itemized list of every consumer’s purchase in a particular month by invoice, enabling them to track and monitor what they have bought (Torrecambo, n.d). As a result, the corporate clients with several divisions will find this report essential since it offers a speed and easy method of keeping track of the purchase made by every department. The reports sort by the customer name, client department, and invoice number. To a greater extent, some of the key database embedded in the monthly customer invoice report includes the client identification number, customer’s organization name, address, consumers city and state, and zip code of their residence, products ordered, quantity requested, and the commodity order date. The phenomenon helps with the massive information concerning the customer’s location and specification of the ordered product. In our scenario, the PEP has the following monthly invoicing report:
Table 1: Monthly Invoicing Report
Output Layout 2 (Accounts Receivable Report)
By the 10th of every month, the customer payments, which act as the lump sum remittances obtained by the PEP, become due. The accounts receivable (A/R) division is responsible for processing the payments. In this case, the department executes its role of updating the client account upon receiving the said payments. The PEP company has the capability of listing unpaid consumer invoices. To a greater extent, with this report, the A/R department can detect the clients owing money and how far they are behind the payment schedule.
Table 2: Accounts Receivable Report
Output Layout 3 (Overdue Collection Report)
These reports are sent to the PEP sales and the A/R departments. However, the invoicing division has the mandate of pinpointing the overdue reports. In addition, the A/R department manages the entire collections that meet the following criteria, including 30 days overdue, 60 days overdue, and above 60 days overdue.
Table 3: Overdue Collection Report
Input Layout
Considerably, to ensure that all the crucial information is entered into the system, effective data entry is key. As a result, the ITSC can acquire all the necessary reports together with other people in rank to access the database.
Input Layout 1
For the good acquisition of the essential information to design the monthly invoicing report, the following data should be entered into the system:
Table 4: Monthly Invoicing Report Data
Input Layout 2
The following data should be entered into the system to obtain a database to design accounts receivable reports properly:
Table 5: Accounts Receivable Report
Input Layout 3
For the good acquisition of the crucial information to develop the overdue collection report, the following data should be entered into the system:
Table 6: Overdue Collection Report
File/Database Design ERDs
The entity-relationship model is a graphical representation that showcases interactions among events, individuals, concepts, places, and objects within an information system. The ERD has substantial merit as it offers a comprehensive visual representation of the layout. As a result, the capability of pinpointing the data flow and the whole system’s operations is made simple by having a well-developed database (The University of Regina, 2017). Secondly, the ERDs ensure effective communication due to clear data depicting the outputs and inputs, which are provided under appropriate tables and sufficient titles. The customers and the employees can adequately understand the link between diverse areas, whereby diverse symbols are deployed to showcase various information types. Therefore, the designer can comprehend the functionality of the database after completion. In the scenario, the PEP company and the consulting entity mutually agree on how the ERD operates and whether it offers the anticipated outcomes. ERDs will help determine the correlation between the monthly invoicing, accounts receivable, and overdue collection reports and how they associate with database outputs and inputs.
The input data elements from the output reports have significant relationships, as shown in the figure. The main entities from the ERD diagram include the customer, cost, product, and overdue fees. The main attributes for the customer entity include the client’s entity, city, state, organization name, primary contact first name, and the secondary contact initial name. There is one-to-many relationship between the orders and the customer entity, whereby one customer can make several orders, but one client can make only one order. Regarding the product entity, some key attributes are quantity, commodity pricing, and item identification number. The relationship between the orders made and the product is that many products can be requested from one order. In addition, the relationship between products and customers is many to many, whereby every consumer can buy many products and several clients can purchase each product. Concerning the overdue fees entity, the primary attributes include past 60 days overdue, 30 days overdue, and 60 days overdue, while those of the cost entity are the total amount, the amount due, date paid, calculated price, and amount paid. The payment relationship between products and overdue fees is many to many, whereby several commodities ordered can attract multiple overdue levies. Nevertheless, the remittance correlation between cost and product is many to many, indicating that several items can have various costs.
Database Design ERD 1
To a greater extent, the monthly invoicing report manifests the existing state from the marketing department as they control the pricing and item codes deployed by the invoicing database on the 15th of every month. The critical data element inputs from the monthly invoice output report have a substantial relationship with the ERD billing and invoicing management system. The monthly invoicing output report has two entities: the product and the customer. From the customer entity, the primary attributes embedded include the client’s organization name, city, state, street address, and zip code.
On the other hand, the attributes associated with the product entity are order date, quantity requested, product price per unit, worker identification number, replacement parts, and the low voltage motor. The correlation between the orders requested and the product is that many products can be made from order. The interaction between the customer entity and the orders made is one to many, whereby a single client can make multiple orders. Still, an independent customer can request only one order. Lastly, the relationship between the customers and products is many to many, indicating that each consumer can buy many products and multiple buyers can purchase every item.
Database Design ERD 2
By the 10th of every month, all the received lump-sum payments from clients are due to PEP. The A/R department is mandated to process the payments and update the customer account records upon receiving remittances. All the collections handled must meet the three criteria: 30 days overdue, 60 days overdue, and past 60 days overdue. The accounts receivable output reports have notable entities: the product, customer, and cost. Here, the attribute of the customer is the organization name, while those of the product include quantity, low voltage motor, replacement parts, and the order date. Several data elements are associated with the cost entity, such as calculated price, amount paid, amount due, product price per unit, date paid, and employee identification number.
The relationship between the orders made and the product is that many products can be requested from a given order. The interaction between the customer entity and the orders made is one to many, whereby one client can make several orders, but a single customer can request only one order. The correlation between the customers and products is many to many, indicating that each consumer can buy many products and multiple buyers can purchase every item. In addition, the payment relationship between cost and product is many to many, indicating that several items can have various costs. It indicates that variable costs can be attached to many products purchased by the consumer of a particular known identity, which are either Condell Medical center or Southern Maryland hospital. In the scenario, all the attributes attached to a particular entity, such as a product, have a one-to-one relationship. For example, every product is assigned to only a particular order date, and the order date can be allocated to a single product requested by a client.
Database Design ERD 3
The role of pinpointing overdue accounts is given to the invoicing department, which then sends them to the sales and A/R departments. The input data elements from the overdue collection report have a substantial correlation, as shown in the figure. From the ERD diagram, there are three crucial entities: customer, cost, and the product, which have varied attributes. For example, the client’s entity has one-to-one interaction with attributes such as city, state, organization name, primary contact first name, and primary contact email. Other elements involve primary contact phone number, primary contact last name, secondary contact initial name, secondary contact last name, and the second person’s email.
At first, the correlation between the consumer entity and the orders is one to many, whereby a single client can request multiple orders, but one customer can make only one order. The relationship between the orders made and the product is that many products can be made from a single order. Furthermore, the interaction between buyers from the Condell Medical center and Southern Maryland hospital and the product purchased from PEP is that of many to many. Due to such, every consumer can buy many products, and multiple consumers can purchase each item. Lastly, the correlation of payment between product and cost is many, showcasing that various commodities can have varying prices. There is a many-to-many relationship between the overdue fees and products, whereby items ordered attract different overdue levies.
Conclusion
The system design specification has described the output and input layout. In addition, it has explained the entity-relationship diagrams (ERDs) showcasing the relationship between the inputs and output data elements. Using the new IT billing and payment management system enables effective communication and easy comprehension of information, enabling solving the issue of processing errors and late remittances and charging. The ERD provides the architecture for designing an appropriate database to solve the issues experienced by the PEP company.
References
The University of Regina. (2017). Data modeling and entity-relationship diagram (ERD). Web.
Torrecambo, D. (n.d). Systems requirements specifications. Project Management. Web.