The NetBeans E-commerce Tutorial - Designing the Data ModelTutorial Contents
This tutorial unit focuses on data modeling, or the process of creating a conceptual model of your storage system by identifying and defining the entities that your system requires, and their relationships to one another. The data model should contain all the logical and physical design parameters required to generate a script using the Data Definition Language (DDL), which can then be used to create a database.[1] In this unit, you work primarily with MySQL Workbench, a graphical tool that enables you to create data models, reverse-engineer SQL scripts into visual representations, forward-engineer data models into database schemata, and synchronize models with a running MySQL database server. You begin by creating an entity-relationship diagram to represent the data model for
the You can view a live demo of the application that you build in this tutorial: NetBeans E-commerce Tutorial Demo Application.
Notes:
Identifying Entities for the Data ModelIn the real world, you may not have the luxury of designing the data model for your application. For example, your task may be to develop an application on top of an existing database system. Provided you do not have a data model to base your application on, creating one should be one of the first design steps you take before embarking on development. Creating a data model involves identifying the objects, or entities, required by your system and defining the relationships between them. To begin identifying the entities we need for the data model, re-examine the use-case presented in Designing the Application. Search for commonly-occurring nouns. For example: Use-CaseCustomer visits the welcome page and selects a product category. Customer browses products within the selected category page, then adds a product to his or her shopping cart. Customer continues shopping and selects a different category. Customer adds several products from this category to shopping cart. Customer selects 'view cart' option and updates quantities for cart products in the cart page. Customer verifies shopping cart contents and proceeds to checkout. In the checkout page, customer views the cost of the order and other information, fills in personal data, then submits his or her details. The order is processed and customer is taken to a confirmation page. The confirmation page provides a unique reference number for tracking the customer order, as well as a summary of the order. The text highlighted above in bold indicates the candidates that we can consider for the data model. Upon closer inspection, you may deduce that the shopping cart does not need to be included, since the data it provides (i.e., products and their quantities) is equally offered by a customer order once it is processed. In fact, as will be demonstrated in Unit 8, Managing Sessions, the shopping cart merely serves as a mechanism that retains a user session temporarily while the customer shops online. We can therefore settle on the following list:
With these four entities, we can begin constructing an entity-relationship diagram (ERD). Note: In this tutorial, we create a database schema from the ERD, then use the IDE's EclipseLink support to generate JPA entity classes from the existing database. (EclipseLink and the Java Persistence API (JPA) are covered in Unit 7, Adding Entity Classes and Session Beans.) This approach is described as bottom up development. An equally viable alternative is the top down approach.
For more information on top down and bottom up design strategies, see Data modeling: Modeling methodologies [Wikipedia]. Creating an Entity-Relationship DiagramStart by running MySQL Workbench. In this exercise, you use Workbench to design an
entity-relationship diagram for the Note: The following instructions work for MySQL Workbench versions 5.1 and 5.2. The images used in this tutorial are taken from version 5.2. There are slight differences in the graphical interface between versions, however the functionality remains consistent. Because version 5.2 incorporates a query editor (previously MySQL Query Browser), as well as a server administration interface (previously MySQL Administrator), you are presented with the Home screen when opening the application (shown below). If you are working in Workbench 5.2, click Create New EER Model beneath the Data Modeling heading in the Home screen.
Creating the
|
Column | Datatype | PK (Primary Key) | NN (Not Null) | UN (Unsigned) | AI (Autoincrement) |
---|---|---|---|---|---|
id |
INT |
✓ | ✓ | ✓ | ✓ |
customer
table by adding the following
VARCHAR
columns. These columns should be self-explanatory, and
represent data that would need to be captured for the Affable Bean business
to process a customer order and send a shipment of groceries to the customer
address.
Column | Datatype | NN (Not Null) |
---|---|---|
name |
VARCHAR(45) |
✓ |
email |
VARCHAR(45) |
✓ |
phone |
VARCHAR(45) |
✓ |
address |
VARCHAR(45) |
✓ |
city_region |
VARCHAR(2) |
✓ |
cc_number |
VARCHAR(19) |
✓ |
VARCHAR
data type, see the MySQL Reference Manual:
10.4.1.
The CHAR and VARCHAR Types.
customer
table selected on the canvas, choose
Arrange > Reset Object Size to resize the table so that all columns
are visible on the canvas. Also click the Indexes row so that any table
indexes are also visible. (This includes primary and foreign keys, which
becomes useful when you begin creating relationships between tables later
in the exercise.)
customer
entity looks as follows.
Column | Datatype | PK | NN | UN | AI |
---|---|---|---|---|---|
id |
TINYINT |
✓ | ✓ | ✓ | ✓ |
name |
VARCHAR(45) |
✓ |
Column | Datatype | PK | NN | UN | AI | Default |
---|---|---|---|---|---|---|
id |
INT |
✓ | ✓ | ✓ | ✓ | |
amount |
DECIMAL(6,2) |
✓ | ||||
date_created |
TIMESTAMP |
✓ | CURRENT_TIMESTAMP |
|||
confirmation_number |
INT |
✓ | ✓ | |
Column | Datatype | PK | NN | UN | AI | Default |
---|---|---|---|---|---|---|
id |
INT |
✓ | ✓ | ✓ | ✓ | |
name |
VARCHAR(45) |
✓ | ||||
price |
DECIMAL(5,2) |
✓ | ||||
description |
TINYTEXT |
|||||
last_update |
TIMESTAMP |
✓ | CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
For details on the TIMESTAMP
data type, see the MySQL Reference Manual:
10.3.1.1. TIMESTAMP Properties.
So far, the entity-relationship diagram contains several entities, but lacks any relationships between them. The data model that we are creating must also indicate whether objects are aware of (i.e., contain references to) one another. If one object contains a reference to another object, this is known as a unidirectional relationship. Likewise, if both objects refer to each other, this is called a bidirectional relationship.
References correlate to foreign keys in the database schema. You will note that, as you begin linking tables together, foreign keys are added as new columns in the tables being linked.
Two other pieces of information are also commonly relayed in an ERD: cardinality (i.e., multiplicity) and ordinality (i.e., optionality). These are discussed below, as you begin adding relationships to entities on the canvas. In order to complete the ERD, you essentially need to create two one-to-many relationships, and one many-to-many relationship. Details follow.
Examine the four objects currently on the canvas while considering the business problem. You can deduce the following two one-to-many relationships:
Incorporate these two relationships into the ERD. You can download a copy of the MySQL Workbench project that contains the four entities required for the following steps: affablebean.mwb.
product
table, then click the category
table. The first table you click will contain the foreign key reference
to the second table. Here, we want the product
table to
contain a reference to category
. In the image below, you
see that a new column, category_id
, has been added to the
product
table, and that a foreign key index,
fk_product_category
has been added to the table's indexes.
category_id
is of type TINYINT
, similar to
the category
table's primary key.
belongs to
'. In other words,
"product x belongs to category y." Note that this is a unidirectional
relationship: A product
object contains a reference to the
category it belongs to, but the related category
object does
not contain any references to the products it contains.product
) can be identified independently
of the parent (category
). An identifying relationship
means that the child cannot be uniquely identified without the parent.
An example of this is demonstrated later, when you create a many-to-many
relationship between the product
and order
tables.customer
and customer_order
objects.order
table first (this table will contain the foreign key),
then click the customer
table. A relationship is formed between the two
tables.is placed by
'. The relationship now
reads, "customer order x is placed by customer y."
You can click and drag tables on the canvas into whatever position
makes the most sense for your model. In the image above, the order
table has been moved to the left of customer
.
Many-to-many relationships occur when both sides of a relationship can have
numerous references to related objects. For example, imagine the Affable Bean business
offered products that could be listed under multiple categories, such as cherry ice
cream, sausage rolls, or avocado soufflé. The data model would have to account
for this by including a many-to-many relationship between product
and category
, since a category contains multiple products, and a product
can belong to multiple categories.
In order to implement a many-to-many relationship in a database, it is necessary
to break the relationship down into two one-to-many relationships. In doing so,
a third table is created containing the primary keys of the two original tables. The
product
- category
relationship described above might look as
follows in the data model.
Now, consider how the application will persist customer orders. The customer_order
entity already contains necessary properties, such as the date it is created, its confirmation
number, amount, and a reference to the customer who placed it. However, there currently is no
indication of the products contained in the order, nor their quantities. You can resolve this
by creating a many-to-many relationship between customer_order
and
product
. This way, to determine which products are contained in a given order,
the application's business logic can query the new table that arises from the many-to-many
relationship, and search for all records that match an order_id
. Because customers
can specify quantities for products in their shopping carts, we can also add a quantity
column to the table.
customer_order
table, then click the product
table. A new table appears, named customer_order_has_product
.
Recall that an identifying relationship means that
the child cannot be uniquely identified without the parent. Identifying
relationships are indicated on the Workbench canvas by a solid line linking
two tables. Here, the customer_order_has_product
table forms
an identifying relationship with its two parent tables, customer_order
and product
. A record contained in the customer_order_has_product
table requires references from both tables in order to exist.
customer_order_has_product
table contains two foreign
keys, fk_customer_order_has_product_customer_order
and
fk_customer_order_has_product_product
, which reference the
primary keys of the customer_order
and product
tables, respectively. These two foreign keys form a composite primary key
for the customer_order_has_product
table.customer_order_has_product
table to
'ordered_product
'. Double-click the customer_order_has_product
table to open the Table editor. Enter ordered_product
into the Name
field.ordered_product
's Table editor, click the Foreign Keys tab. Then,
click into both foreign key entries and replace 'customer_order_has_product
'
with 'ordered_product
'. When you finish, the two entries should read:
fk_ordered_product_customer_order
fk_ordered_product_product
quantity
column in the ordered_product
table. To do so, click the Columns tab in the ordered_product
's
Table editor. Enter the following details.
Column | Datatype | NN (Not Null) | UN (Unsigned) | Default |
---|---|---|---|---|
quantity |
SMALLINT |
✓ | ✓ | 1 |
You have now completed the ERD (entity-relationship diagram). This diagram
represents the data model for the AffableBean
application.
As will later be demonstrated, the JPA entity classes that you create will
be derived from the entities existing in the data model.
Choose View > Toggle Grid to disable the canvas grid.
You can also create notes for your diagram using the New Text Object (
) button in the left margin.
To incorporate the data model you created into the MySQL database, you can employ WorkBench to forward-engineer the diagram into an SQL script (more precisely, a DDL script) to generate the schema. The wizard that you use also enables you to immediately run the script on your database server.
Important: Make sure your MySQL database server is running. Steps describing how to setup and run the database are provided in Setting up the Development Environment: Communicating with the Database Server.
DROP Objects Before Each CREATE Object
', and 'Generate
DROP SCHEMA
'.
DROP
options are convenient for prototyping - if you need
to make changes to the schema or schema tables, the script will first delete
(i.e., drop) these items before recreating them. (If you attempt to
create items on the MySQL server that already exist, the server will flag an
error.)affablebean
schema are included.Note: In examining the script, you may notice that the following variables are set at the top of the file:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
For an explanation of what these variables are, and their purpose in the script, see the official Workbench manual: Chapter 11. MySQL Workbench FAQ.
127.0.0.1
(or localhost
)3306
root
nbuser
The affablebean
schema is now created and exists on your MySQL server.
In the next step, you connect to the schema, or database, from the IDE. At
this stage you may ask, "What's the difference between a schema and a database?"
In fact, the MySQL command CREATE SCHEMA
is a synonym for CREATE
DATABASE
. (See 12.1.10.
CREATE DATABASE Syntax.) Think of a schema as a blueprint that defines the contents
of the database, including tables, relationships, views, etc. A database implements the
schema by containing data in a way that adheres to the structure of the schema. This is
similar to the object-oriented world of Java classes and objects. A class defines an object.
When a program runs however, objects (i.e., class instances) are created, managed, and
eventually destroyed as the program runs its course.
Now that the affablebean
schema exists on your MySQL server, ensure
that you can view the tables you created in the ERD from the IDE's Services window.
Important: Make sure that you have followed the steps outlined in Setting up the Development Environment: Communicating with the Database Server. This heading describes how to run the MySQL database server, register it with the IDE, create a database instance, and form a connection to the instance from the IDE.
affablebean
database
instance you created in the previous
tutorial unit.affablebean
database. To do so,
right-click the connection node and choose Refresh.The IDE is now connected to a database that uses the schema you created for the
AffableBean
application. From the IDE, you can now view any table
data you create in the database, as well as directly modify, add and delete data.
You will explore some of these options later, in Connecting
the Application to the Database, after you've added sample data to the database.
CREATE TABLE
, DROP
, and ALTER
. Other subsets
include Data Manipulation Language (DML), and Data Control Language (DCL). For
more information, see Data
Definition Language [Wikipedia].