View Source

Primary Presentation

Element Catalog

General reference: Elements in capital letters are items of the Pet Store catalog. All others, with the exception of CounterEJB, track customer information.

AccountEJBTable

Contains information about user accounts: status, a reference to contact information, a reference to credit card information, and a reference to its corresponding customer information, if any.

AddressEJBTable

Full streetName, city, state, country, zipCode, and the a reference to its corresponding contact information, if any.

CATEGORY_DETAILS

Includes all the information about a category: category ID, name of the category, the path to an image, category description, and localization information (language-country)

ContactInfoEJBTable

Contains the contact information for a customer: a reference to its address, email, full name (given name and family name), telephone, and a reference to its account information, if any.

CounterEJBTable

Represents a counter with a specific prefix; used only by UniqueIdGeneratorEJB to manage series of unique numbers. Keeps the counter for each table, this follows the Counter EJB Pattern (see reference). It has the current counter, and the name of the table.

CreditCardEJBTable

Contains the credit card information: card number, card type, expiration date and reference to its corresponding account information.

CustomerEJBTable

Contains profile and account information about a customer. It includes the user ID of the customer and its corresponding account and profile information. The user IDs are not restricted to be the same names used in the UserEJBTable.

ITEM

Contains the item's ID and the ID of the product it is classified as.

ITEM_DETAILS

Includes all the information about an item: ID, list price, unit cost, localization information (language and country that the currency is in), path to the item's image, a description of the item and 5 custom attributes.

PRODUCT

This table includes the product's ID and the ID of the category it belongs to.

PRODUCT_DETAILS

Includes all the information about a product: ID, name of the product, path to its image, and a description of the product, and localization information (language and country that the product name and description are in).

ProfileEJBTable

Contains profile information code for the banner preference, the code of the list preference, any language preference -language and country format is expected but this format is not enforced-, a reference to the customer information it refers to (if any) and, the name of the favorite category -not restricted to the CATEGORY_DETAILS names-, if any.

UserEJBTable

Contains log-in information: username and password. The usernames are not restricted to be the same used in the CustomerEJBTable.

Variability Guide

To add internationalization support to the catalog items a separation is established. A table will have an ID and another table will have the localization details for that ID. For example, the CATEGORY table includes a category ID, "FISH", the CATEGORY_DETAILS table will have several entries for "FISH", one per language: English from the US (en_US), Spanish from Colombia (sp_CO), etc.

Other Information

Reflections on the Data Definition Code

Double linked tables. In order to improve performance tables will have column that points back to the record that is using them. For example, CustomerEJBTable has the ID of ProfileEJBTable as a foreign key. ProfileEJBTable has a "reverse_profile_userId" column that links back to the record in CustomerEJBTable that is using it. This relation works because a customer can only have 1 profile.

The information in this architectural diagram represents the information in the code. There are missing constraints that can improve the quality of the data.
Restrict the userIds of CustomerEJBTable to the ones that exist in the UserEJBTable.
Restrict the favoriteCategory of ProfileEJBTable to the ones in CATEGORY.
Create a table to enumerate the possible account status. (AccountEJBTable)
Create a table to enumerate the possible list and banner preferences (ProfileEJBTable)

Reflections on the Manufacturer's Documentation

From Sun's documentation we get a diagram with databases:

Source: Figure 11.6 Functional Modules for the Sample Application Web Site.

The three databases shown in the diagram do not map to the existing ones reviewed in the parent view. The information in those "separate" databases actually reside in the tables from the petstoredb.

Related Views

TBD

Back to the Pet Store SAD main page

Contents