©Mike Moxcey 2002
Making an E/RD: When to Use Designer; When to Use Your Brain
I presented this paper at the Rocky Mountain Oracle User's Group in
February, 2002
This paper provides a basic introduction to system design for developers,
an overview of Oracle Designer’s form and system generation capabilities,
and an in-depth look at its Entity Relationship Diagrammer. We will cover
the logistics and politics of building a really good system Entity/Relationship
Diagram (E/RD)—the foundation for a decent database—and explain how to
fill in the fields in Oracle to generate basic forms for a prototype.
A Good System to Build Good Systems
Developers who have built forms for existing systems, who may have even
added a few tables for extra input or for reporting purposes, may think
they can use Designer to dump a system out the door really quickly. They
can, and “dump” is the operative word here. Which sort of system you build
depends on how the business views your system. All major design decisions
should be made by the business users. If some boss says, “Just do it
and don’t ask me any more questions and don’t bother anyone else neither,”
that’s a design decision. Don’t put much effort into gathering requirements
or testing. If he doesn’t care about putting out a good system, you shouldn’t
either. Do it quick and see if you can move on to some project that really
matters (or to some other company that really cares). Dump it out of Designer,
or write some table creation scripts and forms. The system isn’t going
to be used. Yet even in this case, using Designer would be good because,
if by chance, you get to rewrite the system, it’s far easier if you’ve
got the information documented inside Designer.
Involve the Users
To design a good system, the business users need to be involved. Step one
is to put together a user group with representatives from across the company:
actual workers who create data (even if they don’t input it), supervisors,
upper-level managers, a vice-president, and someone from every single department
who receives reports out of the proposed system (or from the department
creating the system). You’ll need these users for at least a week and a
few of them will have to be on call for several months. If management isn’t
willing to fund a user group like that, then that’s a major design decision.
See earlier section. Actually, in this situation, you’d want to develop
a good system because it’s going to be used by the line workers no matter
what. This is how many current systems were developed. To get valid requirements,
interview users on the sly.
Get Good Users
One major problem is some managers will cut corners on the user group by
sending someone they can “spare.” You know the kind. Someone who can be
gone for a week or month and no one will miss them. DON’T ACCEPT THOSE
KINDS OF USERS. You’d be better off simply walking around interviewing
people who look busy and developing the system yourself. To convince management
to fund a well-selected user group, explain that the group will be making
all design decisions. Call the group a System Oversight Board. Tell management
that computer people can’t make good decisions about business needs. Explain
that if this system is going to cost X amount of dollars and last for this
many years, then investing up front in a good user group is crucial. They
need to pay dollars for the travel and use political muscle to convince
managers to send the selected employees. The IT group should create a list
of user types: 3 line employees with skills in certain areas, 2 data entry
clerks, 2 line supervisors, 1 VP, someone from the warehouse, and an executive
secretary with at least 10 years experience. Then the management team ought
to select the best workers to fulfill those needs. Granted, this is an
ideal situation, but if you want your system to be successful, then you
must aim for success at every step. You can work around poor management
by pulling together your own group of users on the sly, but you can’t build
a topnotch system for the company if the company doesn’t invest its own
time and effort into it.
Running a User Group
Once you’ve got a user group, you must train them. If you’re doing process
re-engineering or trying to figure out what it is you want to track in
the first place, then you’ll be training the users in process modeling
and using the Process Modeler and/or Dataflow Diagrammer in Designer. But
if you’re building a database, or redesigning an existing one (which is
probably the case if a developer is in charge of this effort), then you’ve
got to train the users to think of entities and relationships. Explain
that a database tracks information. It keeps data as rows in tables kind
of like a spreadsheet. But a well-designed database keeps you from having
to repeat information and also prevents information from getting mishandled.
But before you go into the user group, you ought to be well-versed in entity-relationship
modeling yourself. A good book for that is The Data Model Resource Book
by Silverston, Inmon, and Graziano. You won’t need to explain (or use)
all the details in that book, but you’d better understand them so you can
explain the principles to users. Chapter 4 is particularly enlightening
about how the standard purchase order model taught in computer science
classes is wrong. The book may make you think you can drop the user group
and create the system. Don’t. There are many decisions and you want an
E/RD that reflects the specific needs of the business paying for the system.
Entities
Tell the users the first thing we want is a list of entities. These are
things we want to track information about. They are nouns. An entity is
a person, place, thing, event, or concept. An event could be an airplane
flight or a concert. A concept can be an invoice or an account. Don’t explain
at this time why an account entity won’t exist in the system. We’re trying
to get an initial list of things that are important to the business. If
you’ve got a good group with management backing, then you’ll probably have
a professional facilitator and note-takers. During this brainstorm (standard
technique of no criticizing but asking for more information is allowed),
one person needs to be writing the nouns up front. One other developer
ought to be writing down the verbs. These will end up as processes if you
track them or in the test scenarios you’ll be writing. If it’s a small
system or management isn’t willing to invest any time but you are, you
can do this same thing by interviewing users. The brainstorming won’t be
exhaustive, but will be useful.
Attributes
After the initial entity brainstorm subsides, you can explain that attributes
are things that describe entities. For example, an employee will have a
name, rank and serial number. A flight might have a number, date, time,
and amount of passengers. A concert may have a date, time, venue, and headline
act. Go over the list of nouns on the board (or more likely on paper taped
all around the room), and try consolidating them. Some nouns are probably
the same thing. The discussion on what aspects are the same and what are
different is a critical one to have. LISTEN carefully. The developer or
designer should not be talking at this time. You are discovering what’s
important to the business users and why it’s important. These discussions
may take a day or more. During them, new nouns may be added to your list.
Verbs and scenarios will be added to the list kept by a cohort. After awhile,
the group will come to a consensus about which are entities they care about
and which nouns are attributes of other entities. At this point, you’ll
have a list of candidate entities. Do not put them into Designer. For one,
it’s not a good on-the-fly tool. Creating an entity creates lots of stuff
in the background. But the main reason is that this list will still have
an Account entity on there. You could have explained earlier why it won’t
exist, but that confounds the users which tends to prevent good brainstorming.
Relationships
Now is the time to explain how relationships in a database work. The users
have worked with you for a day or more and are beginning to enjoy trying
to think like philosopher-geeks. They trust both you and the process a
little more, and are feeling comfortable with database concepts. I use
an example of the entities Songs and Authors to explain these concepts
(See Introduction to Database Design on my
web site at http://home.att.net/~mike.moxcey/pgm/
). The power of a database lies in the relationships between tables. You
can pull up an employee and use links (relationships) to discover all the
work she's done in the past year on a variety of accounts and machinery.
Foreign Keys
Starting with a Song entity and an Author entity, you explain how a link
is created. You don't want to carry the Author's last name in the Song
table. What if you had three Authors: Stephen Foster, Barry Foster, and
Suzy Foster? What you do is create an arbitrary column called ID (or SEQ_NR)
that is a number (from a sequence generator) and then each Song record
will have an attribute (also called a foreign key) which holds the ID of
the appropriate Author record. Show them an example with attributes containing
values. Explain how relationships have two key factors: optionality and
cardinality.
-
Is the relationship optional or is it mandatory?
-
Is there one related record or can there be more than one?
The users have to truly understand these concepts. If they think a relationship
exists between two entities, then they need to answer the questions of
optionality and cardinality in each direction. There are 4 questions for
each relationship. The answers determine the entire functionality of the
database.
Optionality
For any relationship that exists between two entities, you must answer
this question: For a given record in entity 1, MUST there be a record in
entity 2 or is it optional? Then you ask the same question again from the
perspective of entity 2. If a record exists here, does a record have to
exist in entity 1? Using the Song example, we would ask that if a Song
exists, MUST it have an Author? The philosophical answer is: Yes, every
Song has an Author someplace. The real answer is: There are lots of Songs
I'll enter without Authors. One concept to get here is you're looking for
the exception of the MUST. Will there ever be a record that doesn't need
joining to the other entity? Now look at the other end. Will an Author
exist without having written a Song? At first, I'd say no. The only reason
to put an Author in is because he's written a Song I want to track. However,
if the join is required, then I can enter an Author only if I entered his
Song first. There may be ramifications for data entry that make a relationship
optional when it seems at first it ought to be mandatory. The main thing
we're looking at here is what does the business have the will and means
to capture data about? That's why you ask users these questions instead
of developing the system in a vacuum of ideal philosophies and data principles.
Cardinality
The Cardinality question is: Will there be at most one record at the other
end of the relationship or could there be more than one? We only care about
one or more than one so we only need to look for an example that has 2
or more joined records. For the Song entity, the question would be: Could
a Song have more than one Author? Of course. Just think of the classic
teams of Rogers and Hammerstein or Lennon and McCartney. This is called
a MANY relationship. Now ask the question from the other side: Can an Author
write more than one Song? Of course, so this is a MANY relationship in
that direction, too.
Associative Entities
You cannot have a Many-to-Many relationship. These are resolved using an
Associative Entity. In this example, we'd create a Song_Author entity that
has only a foreign key for each of the other two tables:
Song_Author_Association
Song
Seq_NR
Seq_NR............Song_Seq_NR
Author
Name
Author_Seq_NR.................Seq_NR
History
Name
History
Explain how these are drawn on the diagram with a crows foot representing
a MANY relationship coming into that entity and a dotted line means the
relationship going out from that entity is optional. The users really have
to understand this because they need to verify your model. Show them the
two separate relationships first:
Then show them how they will appear as one line in Oracle Designer.
Once the entities and relationships are entered into Designer, there
are English language reports the users can read , but you don't want to
get into Designer until the relationships are fairly stable. Use a white
board or write on transparencies.
No Account
There will be much rewriting. Many bogus relationships and entity arrangements
will occur until understanding jells. Explain how Account will only be
an ID_NR in an associative entity and the necessary data will be kept in
other entities such as Customer, Address, and Purchase. Tackle pieces of
the diagram. Try to get all the Employee info tied together well. Then
look at Equipment or some other area. Once you get a few areas tied down
reasonably well, you can even put them into Designer (finally, the developers
get to do some "real" work). These partial diagrams can be printed out
on paper or transparencies to see how they tie together with the other
pieces. If they can, users ought to name the relationships. This helps
further define why the relationship exists and is useful when looking at
the model and reading the reports later.
Intelligent Keys
Explain to the users that a Primary Key makes the record unique in the
database. Because a Primary key must never be updated, we don’t use user-entered
information, such as a customer name, address, or account number. We make
those Unique Keys. The user group should define Unique Keys; the database
should create Primary Keys. Users often want an invoice number made from
the first 6 digits of the customer name followed by the employee number
who took the order followed by a date. Because it contains data inside
itself, these are called “intelligent keys.” Do not keep them in the database.
Show the users you can output their intelligent key on reports for the
use of warehouse workers, but the data it contains will be kept as separate
fields.
Using Designer (finally)
You can send the users back to their day jobs now (or do this at night
between meetings). Most developers want to get in and develop in Designer
right away, but to build a good system, you need a good plan and the people
that are going to use the system are the ones who must develop the plan.
Once they’ve got a good start on the plan, then you can start putting it
into action in Designer.
Making Work Areas and Containers
To create an E/RD in Oracle Designer, the first step is to open Designer.
This assumes Oracle has been installed into a repository and you've got
access to it. Then you create a Work Area (usually your application name
even though Oracle wants that for your container name) and start there.
On the initial Oracle Designer 6i screen, choose the Entity Relationship
Diagrammer (in the Model System Requirements section). When the E/R Diagrammer
comes up, choose File;New and a little window will pop up telling you to
select a container name. Double-click on the page icon to the right of
the text box and a new screen will appear. Click on the Create (new container)
icon on the top toolbar and give it a name such as version one or APP(1).
Click Ok until you get back to a clean new diagram tentatively labeled
ERD1. Name the diagram ERD_something_or_other. This will group all the
ERD diagrams together in the RON (Repository Object Navigator).
Add Entities
To create an entity, click on the Entity icon (an oval). The pointer should
change to a box when you move back over the diagram. Click anywhere and
a Create Entity window will appear. Put in the Name, Short Name, and Plural.
Short Name is used as a prefix to indexes and keys.
Plural is used as the table name and in the entity reports. Using an actual
plural makes reading the reports a bit easier, but you're going to live
with those table names for a long time so choose the table name you want.
(I personally don't like plural names).
Add all the entities at once. If there are more than 20, you might want
to group them by subject area on separate diagrams and pull them all together
later. To add multiple entities easily, lock down the entity icon by pressing
the Shift key before clicking on it.
Create Relationships
Once you've got all the entities needed for the current diagram, put in
the relationships. This is trickier. To create a relationship, select a
relationship icon, click on the from entity, then click on the entity it's
going to. You can fix relationships later by right-clicking on one and
selecting either one of the 4 options or clicking Properties and fixing
it there. There are 9 different relationship icons to choose from. A couple
(the Many-to-Manys) are only used for strategy E/RDs, not for ones to convert
into tables. The rest must be used with the left side starting on the initial
entity. Select the correct relationship, connect it from one entity to
another, and a window will appear asking for the from and to names of the
relationship. There are lots of issues around selecting good verbs. An
easy way for most relationships is to choose a gerund (ending in "-ing")
such as "assigning" or "describing" and use it for both sides of the relationship.
These names are used on reports to enlighten readers; they won't affect
your database.
Finishing the Diagram
Making the diagram readable is a chore. It seems like the Edit;Select All
(Ctrl-A) and then doing Layout;Autolayout (icon) would make it all simple.
Try it and see if you like it. (CTRL-Z is Undo). The way that works for
me is to
Move and resize entities,
Click on a relationship,
Choose Edit; Select Same Type
Click the Autolayout icon (or Layout;Autolayout).
After a few iterations, the diagram becomes readable. Then you can click
on the relationship names and move them individually when needed. They
are automatically moved when doing an autolayout of relationships so wait
until you're done to move them.
Domains
Enter the attributes, but before doing so, decide on their length and type
and apply that information using domains. It's much easier to make changes
and to document properties using domains. You can create domains as you
create entities. You can also create domains by themselves from the RON.
Expand your container, then right-click on Domains and choose Create Domain.
The only things you have to enter are Name, Datatype, and Maximum Col Length.
Use some good Domain Naming conventions or you won't be able to figure
out where to go to fix something. I preface all the domains that have actual
values with LKP_ followed by the attribute they are the lookup for. Attributes
Now enter the attributes. You could also have entered
them while putting in the entities, initially. You can create attributes
in the RON, but for the first pass, I find it easier to enter them off
the E/R Diagram.
Open a diagram and double-click an entity (or right-click and choose Properties).
Click on the Attributes tab and fill in the information.
I like having the same Name for similar attributes so I usually make a
list of them ahead of time. You can also change them after. The Seq column
is numbering for the order in which the attributes are displayed in the
RON and in the form. Use them. The generators really want that data. Check
Opt if the field will be optional, otherwise the field will be required.
Don't worry about anything else on that tab. DO NOT ENTER IN ANY PRIMARY
KEYS. I suppose if you want to you can. I like Primary keys based on generated
sequence numbers. This is what you get if you do not specify any primary
keys at all; don't check the Primary box at the far right under the Attributes
tab nor under the UID tab. Oracle will generate a primary field called
ID with an associated sequence for it and the field won't be displayed
by default on your form modules. Definitely the way to go. Click on the
Att Detail tab to apply domains or set the type and lengths of fields.
Ignore the Derivations and On Conditions because this is for a quick build
of a prototype. Before you select a Domain, click the Apply button at the
bottom to apply your new attributes. The current attribute is listed in
the top of the window in the Name field. There is a dropdown list to switch
to other attributes. The Att Values tab allows you to put in specific values
for an attribute, but I think you're better off doing that through domains.
The UID tab is where you put in Unique Identifiers. Again, do not click
the Primary box if you want Oracle to generate your primary keys and sequences.
You can put in a Name for the Unique Identifier (I use the column/attribute
name) and then click on a candidate attribute and use the down arrow to
send it down to the Unique Identifier Contents block. In the Definition
tab, you can change the entity names and put in values for table sizes.
Under the Text tab, you can put in Descriptions or Notes (change using
the text type window) about the entity or about specific attributes (change
the top window to "Attribute" and an attribute selection appears). The
help screens are succinct, but useful.
E/R Reports
Once you've got the model fairly well done, you can print out reports.
I don't particularly like them; there is too much detail. However, they
are useful for giving to users and filling up notebooks for managers. To
run reports, get on the initial Oracle 6i screen and choose Repository
Reports in the lower righthand corner. Open up the Entity/Relationship
Modelling to view the 8 available reports. I use the first four about entities
and attributes. The reporting, as usual, is very funky. For some reason,
Oracle doesn't seem to care about getting data out. One of the best ways
to use the canned reports is to run them in Previewer. You can fool around
with the options on the Parameters Palette of writing directly to a file
but certain options overwrite other ones. After seeing if the report looks
good in Previewer, choose File; Generate to File and select the format
and enter a directory and file name. If you don't like the reports, Oracle
recommends you open the report file in Developer and change it yourself.
For the users, the reports are good. I output them in HTML and wrote a
Perl script to strip them. For short lists of info for myself, I looked
inside the reports for the tables and went into SQL and wrote some quick
little queries. The SQL information is on my web page.
Testing the Diagram
Finally, test your diagram. One way is to go over all the entity definitions
and relationships with your users. This is time-consuming and boring, but
very useful if the users put effort into it. The other way to verify it
for yourself is to use the scenarios you've gathered.
Scenarios
As you're going along with your users getting nouns and naming relationships,
there will be lots of discussions about how work is done and how data is
entered or how managers use the data to answer questions. CAPTURE ALL THAT
INFORMATION. Write it down and save it. Those scenarios of how work is
accomplished will tell you whether the model you have works. If you've
written them down, then you don't even need the users to test the model.
If it works for the scenarios you've captured, then you can give those
to the users and say, "We can get this data in and out of the system. Do
you have any more scenarios that we haven't covered?"
Tweaking the Diagram
You can fine-tune the diagram yourself.
Get rid of all the weak entities--ones that have only a single relationship
to another entity. Fold them into the other entity as an attribute (usually
with an associated lookup domain).
Look for commonalities of data. The Data Model Resource Book mentioned
earlier is helpful in this respect. See if you can't meld customers and
businesses and employees together. Sometimes you can and should, sometimes
you can't. They might be holding different types of data or have different
relationships to other entities.
Look for data you don't really need. You'll probably have employees in
there and employees have spouses and addresses and phone numbers, but is
this database the place you want to keep that information? Unless it's
a human resources database, the data probably resides elsewhere and putting
it in your new system means double data entry.
Investigate the ways you're going to classify data. If a record in a table
(an instance of an entity) can only be one thing, then you can make a Type
or Class code for it. But if it can be classified as two or more things,
then you will either need multiple Type fields, or you will need to add
a set of hierarchy entities so the same record can be classified in multiple
ways. For example, in a wildlife damage database, we assumed we'd only
need the separate Linnaean species codes. However, there are scenarios
where the users care about subspecies, need to distinguish between domestic,
feral, and free-range animals, and must know if the species is Threatened
or Endangered which can change from state to state or county to county.
Test with the scenarios again after making all these changes.
Building the System with Designer
With the E/RD done, you can create a system using other Designer tools
to create Functions, create Tables, and turn the functions into Modules
that use the tables.
Function Hierarchy Diagrammer
Now you're ready to build the system (or a prototype of it) in Designer.
First, create a good Function Hierarchy Diagram (FHD) using the Function
Hierarchy Diagrammer. A Process Model would give you a bunch of functions
to work with. But for a database, you don't need a comprehensive FHD. Put
in a function for every form. In the Properties for each function, you
define the Entity Usages: Create, Retrieve, Update, and/or Delete (CRUD).
Some forms will be large and their CRUD may include several different entities.
You can also create functions for reports and batch processes if you want.
The hierarchy of functions makes it easy to generate modules so you don't
need to put too much time into analyzing it. After assigning Entity Usages,
run the Utilities; Function/Attribute Matrix to assign IRUN (Insert, Retrieve,
Update, Nullify) for each attribute of each entity.
Make Tables
You must have the tables in place before creating modules. Making the tables
is a multi-step process:
Run the Database Design Transformer (in the Transform Preliminary Designs
section) to create Relational Table Definitions. This will also create
Sequence Definitions if you haven't designated any primary keys.
Get into Design Editor (in the Design and Generate section) and run the
Generate; Generate Database from Server Model to create SQL scripts to
create your tables, views, indexes, and sequences. You can put a table
prefix in here to distinguish your tables from everyone else’s.
Run the table creation script you generated inside SQL*Plus.
Create Modules
In the Transform Preliminary Designs section, choose the Application Design
Transformer to create modules. Select the top function and it will generate
modules named with the prefix you give and numbered starting with the number
you enter.
Design Editor
To see what you've done, go into Design Editor. The Server Model tab will
have your relational table definitions. The Modules tab will have those
and your modules. They will be oddly named and numbered (which is one argument
for generating modules a few at a time) but if you expand them, you can
tell which is which. Rename them and uncandidate them (in Properties, change
Candidate? to No). Then you can drag the newly changed icon onto the Design
Editor's gray screen and begin making your forms—still a major task.
Regenerating
You'll never get the first pass right. In order to regenerate, you must
delete everything or they won't regenerate.
You can leave old modules around but they fill up the RON and Design Editor.
Drop the tables in the database. You can write self-creating scripts to
do this (based on the table prefix) or you can create all tables under
a specific user and then drop the user.
Delete the definitions for the tables, indexes and sequences. If you only
want to regenerate a couple, then you can leave the rest and Designer won't
map them in the generator. If you don't delete definitions, then you may
end up spending too much time looking at the generator options trying to
figure out what's wrong.
Small Steps
For the first time through in Designer, make separate workareas. One should
hold your main application and the other can hold a few test tables so
that dropping, changing, and generating doesn't take so long. The help
screens are useful, but sometimes you need to see the whole thing through
to the end to truly understand what one little change will do.
Summary
Oracle Designer is a complex tool. System design is a complex process.
Together, they make some of the work less tedious but you must still use
the organization’s collective brain power to create a good system. You
must also invest quite a bit of time in learning to use the tools available
in Oracle Designer. This paper and more information about system design
and Oracle Designer are on my programming page at http://home.att.net/~mike.moxcey/pgm/.