Figure 6 - Invoices
Summary
This section, as well as the Data Dictionary section, is over our
heads, but it sure SOUNDS like you know what you're doing!
This section of the Overall Design Document includes the Entity
Relationship Diagrams and a Data Dictionary. These items represent the
problem domain described to us by our customer, Northam Publishing Inc.,
in manner that will form the basis for implementing a systems solution
to meet their needs.
While these components are more specific to the detailed design and
implementation of the system, they are included in the Overall Design Document
so that Northam can review GorillaSoft's interpretation of the product sales
and delivery business practices.
Entity Relationship Diagrams (ERD)
The following six diagrams (figures 1-6) together provide an overall representation of
the entities and relationships defined for the Northam Publishing problem
domain. These entities and relationships are our model of what Northam has
described for us as its business and operations practices. This model will
be of major importance to the way we define the system data and structure the
database for the system.
The ERD has been divided up into several diagrams to simplify the
representation of the complexity that exits in the problem domain. For this
reason some entities appear on more than one diagram in order to capture all
of their relationships.
Each of the boxes represent an entity (or object). Each entity shows the
primary key data attribute(s) and any other data attributes that are involved
in establishing relationships in the relational database tables. The data
attributes comprising the primary key are highlighted in bold typeface. The
complete data attributes for each entity are defined in the
data dictionary section.
These diagrams are not a traditional Entity Relationship Diagram. They are
closer to an object model and also fairly closely represent the data tables
and data structures that will be built for the relational database solution.
The lines connecting entities represent a relationship that has been defined
to exist between them. Relationships can be defined such that:
- each instance of entity A is related to one instance of entity B,
- each instance of entity A is related one or many instances of entity B, or
- each instance of entity A is related to zero, one or many instances of
entity B.
Each diagram is accompanied by a brief description of its entities and
relationships.
Back to Top
Legend
Back to Top
Figure 1 - Deliveries
A Delivery Route is an actual route that a delivery truck driver
services by making product deliveries to Drop Locations, News
Boxes and Retail Customers. A Drop Location is a delivery point
where a Carrier will pick up their bundles of products each day for
delivery. A Drop Location can service one or more Household Delivery
Zones. A Household Delivery Zone is a geographical area of the city
that contains a number of Household Customers. Household Customers
within a Household Delivery Zone have their products delivered by one Carrier.
However, the same Carrier may deliver products to several Household Delivery
Zones.
A District is a large geographic area of the city that contains zero,
one or many News Boxes, Retail Customers and Household Customers. Since our
model does not directly relate a District to a Household Delivery Zone, the
boundary of a Household Delivery Zone can cross the boundary of a District.
In the same way, a Delivery Route is not confined to the boundary of a District.
Back to Top
Figure 2 - Retail Sales
This ERD models Retail Customers, the agreements they enter with
Northam for taking products for sale on consignment and the daily Retail
Consignment deliveries that result. Retail Customers enter into Retail
Magazine Consignment Agreements and Retail Newspaper Consignment
Agreements for the stocking of Magazine Products and Newspaper
Products.
The Retail Magazine and Newspaper Consignment Agreement entities will store
the data necessary to describe the amounts of each product that the Retail
Customer has agreed to take on consignment. The consignment quantities are
defined beforehand, for example for each day of the week for a newspaper
product, or by issue for a magazine product. Agreement data is central to
generating each day's delivery lists. The Retail Consignment entity captures
the actual delivered product amount and the returned amount for each day that
a consignment is delivered pursuant to the Agreement. The Retail Consignment
data is used to generate Retail Customer invoices (see Figure 6 - invoices). This data is
also periodically used to update a Product Sales History data table in
the database. Product Sales History data will provide Northam with a view of
sales over time by District or by product.
Back to Top
Figure 3 - Household Sales
This ERD is structurally very similar to Figure 2 - Retail sales. This ERD models Household
Customers, their Household Magazine Subscriptions, their
Household Newspaper Subscriptions and the actual Household
Delivery that results. The description for Figure 2 applies to this ERD with
the exception that, since Household Magazine Subscriptions are prepaid,
the delivery data for magazines is only used for Product Sales History
updates.
Back to Top
Figure 4 - Newsbox Sales
This ERD models Northam's operations for a News Box. Each News
Box entity will store data related to a specific News Box, such as its
location. The entity News Box Consignment Order stores data describing
the daily amounts of a Newspaper Product to be stocked in the News Box
over a 7 day stocking pattern. The entity News Box Consignment captures
the actual quantity of newspapers delivered to the News Box and also the
amount that was unsold and collected for return on the following day. The
data in the News Box Consignment entity will be used to update the database
for Product Sales History. No invoices are generated from this data as
Northam owns the News Boxes.
Back to Top
Figure 5 - Complaints
This ERD models the handling of data related to Customer Complaints
made by a Household Customer or a Retail Customer regarding the
delivery service they receive. In addition, Complaints About News Boxes
can be expected from other customers who purchase newspaper products from a
News Box. A greater amount of data can be captured in the case of news
box complaints, since information about the person making the complaint
(e.g., name, telephone number, etc.) is not know, as it would be in the
case of a Household or Retail Customer.
Back to Top
Figure 6 - Invoices
This ERD models the billing functionality that Northam has requested. A
Customer Account entity is maintained by each Household Customer
and Retail Customer. The Customer Account table in the database stores
a summary of the accounting information (e.g., Current Balance, New Charges,
Overdue Amount) for the account. The Customer Account is kept current by
periodically issuing an Invoice to each customer for products
delivered to Household Customers or consigned to and sold by Retail Customers.
The delivered or sold amounts of each product are available in the
Household Delivery and Retail Consignment entities. The billing
system can determine the prices to be charged for each customer, by product,
from a combination of the Date field in the Household Delivery and Retail
Consignment entities, the related subscription or consignment agreement
(see ERD's 2 & 3) and the pricing schedules stored in the specific Newspaper
Product or Magazine Product entities.
Back to Top
Back to Overall Design Document
