Important Considerations About the ERD Diagram

After analysis and design meetings, the development team of Team5.com decided the following things in order to prevent future problems.

1. A customer may store multiple shipping addresses in his account.
2. The customer may edit addresses, which can change completely, or partially, the original addresses. For this reason, as a business rule, it was decided that orders will not simply point to a specific shipping address, on the contrary, they will stored the complete address information. This means that when an order enters the shipping process, all the values of the chosen address will be placed into the current order, and the system will not allow any updates, from the customer, in this order anymore. This way if a customer change the values of an address all the orders placed before this change, that used that address, won’t be inconsistent and referencing to wrong information. It’s important to clarify that Team5.com will transfer to the customer the total control to manage their addresses, but the company will guarantee the right information on shipped orders, avoiding future complains and problems.
3. Shipping addresses will be composed by street, city, zip code, state, country and a field called "recipient name" in order to allow the customers to send orders to other people besides themselves.
4. The price of a collection coin depends on the price of the material that it is made of and the price of the material changes constantly. Because of this reason, and also in order not to have to create a history of changes on product price, Team5.com decided to duplicate the price of the product inside of table OrderLines. By placing the price of the product inside of table OrderLines, when the orders are shipped, will allow the company to know exactly how much was the cost of the product when it was purchased. This will also allow the price of the product in table product to be changed as many time as necessary, without affecting old orders that were placed before the price update.
5. It’s important to remember that once the order enters in shipping process further modifications will not be allowed.
6. It’s important for Team5.com to have the right information on shipping addresses and product price when orders are shipped. This information will be valuable when the company decides to implement in the future a data warehouse to guide its business.
7. All the tables, except table OrderLines, have as primary key an identification number generated automatically by the system. The table OrderLines has for primary key the combination of the identifications of table Product and table Orders.

Important Considerations About the OR Mapping Design

Strategy for Persisting Objects

The process of retrieving information from a database to populate objects and persisting the objects information back into the database tables is called object relational mapping. The development team of Team5.com decided to implement the object relational mapping for this e-commerce site using "Manager Classes". These classes, integrated with a database connection class, will be responsible for the flow of information between objects and database, and vice versa.

Database Choice and Technical Considerations

The database chosen for the project is Oracle 8, and the contract has two major aspects that influenced on the OR mapping process. The first aspect is that the version purchased by the company does not have object oriented features enabled. This will not allow objects to be mapped directly to objects. They will be flattened to primitive data types inside of table. The second aspect is related to the limited number of connections that the database accepts. In order to handle this issue, it was decided, by the development team, that every manager class will establish connection with the database, perform all the transactions necessary for a specific functionality, and closes the connection at the end of the transaction. The connection will be opened only when necessary, due to an operation of retrieving or persisting information in the database.

Manager Classes

The project has a total of 4 core manager classes as listed bellow:

ShippingMethodManager Responsible for mapping shipping method information.

ProductManager Responsible for mapping product information.

OrderManager Responsible for mapping purchase orders information.

CustomerManager Responsible for mapping customer information as well as shipping address information

Aggregations

The class OrderLines is aggregated by class Orders. This is a STRONG aggregation once OrderLines depends on class Orders to exist, be accessed and managed.

The DBConnection class is aggregated by the Manager classes in a WEAK composition. The DB Connection class, is an independent class that does not depend on any class to exist, and can be used or aggregated by any other classes that require a connection to the database.

Orders class, in a WEAK composition, aggregates the Address class. The Address class, is an independent class, it does not depend on any class to exist and can be used or aggregated by other classes that requires it.

Unique IDs via  Oracle Sequences

The database has a total of 5 sequences that are used to generate unique IDs for new persisted objects. The sequences are called in the "insert" statements:

OrdersIDSeq;

Inheritance – Address and Shipping Address

Address is a supper class for Shipping Address class. The option adopted to implement this inheritance into Oracle8, once its operating as a RSDBM, and not ORSDBM, was to put everything into the same table. This only one table that joins all the information from both classes is called "ShippingAddress".

The OR Mapping Diagram does not show all the attributes and methods used by the core classes, and the manager classes, because the purpose of this document is to focus and provide a view on the mapping process. The OR mapping diagram is still under construction and some aspects may change due to necessities