|
Developer TRENDS
Designing Schemas
By Cameron O'Rourke
Applications come and go, but data lives on forever.
When you build an application, do you start with a database schema that has been solidly designed and tested? Or does your application begin its lifecycle on a wobbly foundation, perched atop a machine-generated schema?
In this article, I hope to convince you to view database schema design as a vital part of creating any high-quality application. I'll show you what my typical workflow looks like and provide you with a few tips that I've found helpful.
Is It State or Date?
Although object-oriented technology has been a boon for application developers, it has caused some setbacks for data management.
In the world of objects, data is viewed as simply object state, a means by which to implement proper object behavior. Good object design seeks to encapsulate or hide the object state behind
an interface (a set of method calls).
Modern object/relational frameworks—which map object state to
relational tables—attempt to shield object-oriented developers from needing to design a relational database, create SQL statements, and understand the capabilities and performance characteristics of their chosen data store.
Frameworks such as Java 2 Platform, Enterprise Edition (J2EE) Entity Beans are able to generate a database schema suitable for persisting the object state. At runtime, SQL statements are automatically constructed to insert, update, remove, and query database rows.
But is any of this a good idea? What do you give up when you forgo data modeling, relational fidelity, handcrafted SQL, and vendor-specific
database features?
Don't Generate—Model
The fact is that a machine-generated schema simply can't match the careful design produced by a good data modeler. By understanding the data requirements of a business domain, a data modeler creates schemas that transcend the current application. A good schema abstracts the specific application constructs to address a wider class of business problems and to better endure changes in the environment over time.
|
| Figure 1: Conceptual view of Designer repository and tools
|
"Application developers must learn somehow that they do not own the data; the company owns the data, and their application is just 'borrowing'
it," says Tom Kyte (asktom.oracle.com). "Their application might not live,
but the data will be there forever, and their application will not be the last thing to need it."
Your SQL, Your Database
Many persistence frameworks give you little or no control over the SQL that is executed. This limits your ability to tune the SQL and to express precisely what result you are looking for. The database is very efficient at providing answers, but only if you tell it through an appropriate SQL query what you are trying to accomplish.
Another limitation is that applications often require queries that are more complex than a framework can generate, such as a query to roll up costs in a hierarchical task list.
Choose a framework
such as Oracle Business Components for Java (BC4J) that allows you to override the SQL used for queries. Other strategies for controlling SQL include encapsulating all SQL in database stored procedures and making extensive use use of views in place of base tables.
Generated schemas rarely make intelligent use of relational database features, and this can have a huge impact on performance. The database is very efficient at returning results, but it can do so only when you give it options. Relational joins, views, aggregates, subqueries, CONNECT BY, outer joins, and so on are all very useful to the cost-based optimizer
and are all generally ignored by
persistence frameworks.
Few vendor-specific database
features will be used in a generated schema. Specialized functions for math, string handling, security,
external processing, space and time calculations, and statistics and analytics will all go unused by many frameworks. Not taking advantage of all
the database has to offer adds up to more code and more costly development and maintenance.
Model with Oracle Designer
Although there are many tools out there that can model a basic schema,
I use Oracle Designer because it has
the best support for Oracle database structures. With Designer, I can model a complete Oracle schema including views, object types, sequences, check constraints, users and roles, database triggers, and stored procedures.
Oracle Designer is a collection of diagramming tools, model transformers, and code generators that all work against a rather extensive repository. There are about 50 different object types in the repository. In Figure 1, I've categorized and shown them in relation to the Designer tools.
Designer segments the repository into conceptual objects and physical objects. The conceptual objects (shown in the upper half of Figure 1) are useful for capturing the business model, cataloging requirements, and doing application design. The physical objects (shown in the lower half of Figure 1) contain a lot of detailed information and are used to generate actual code assets and database schema objects.
I typically start a new application by creating an entity relationship diagram (ERD). Entities are conceptual data structures that let you think about the data—and the relationships between that data—that your application will need to handle without thinking about how the data should be manifested
in rows and columns. There is no
need to model every single attribute
at this point and no need to include primary/foreign keys. The ERD is also perfect for high-level discussions with my business users.
At any point, you can run the Data Design Transformer, and it will generate a set of database tables for you, complete with prefixed column names, indexes, and primary/foreign keys. If you had modeled super- or subtypes or any many-to-many relationships, it will resolve those constructs into an appropriate relational table structure. Best
of all, this is not a one-time operation—you can rerun the Data Design Transformer as you update your model. It always shows you what it is going to do so that you can change its proposal and avoid clobbering any work done on the schema.
Next, I model the schema using the Design Editor. The relationships between the tables should have already been established from the ERD, so here I mostly flesh out the details. (If you do nothing else but use the Design Editor to populate the storage model and generate your database schema, you will still have a much better foundation than many development projects have.) Here are a few tips:
- Enter comments for each table and column. These comments will be transferred to the database, and you can use them as help text in your application. Describe what the table or column represents in the real world, including the unit of measure for numerics. Be sure
to document what a NULL value means.
- A domain is a named set of column parameters. Use domains to maintain consistency, especially for primary/ foreign key columns.
- If a column takes on discrete, coded values, specify them fully in the Allowable Values dialog box. Designer will create a table containing all of the coded values that you can use as text for popup lists and help.
- If a column will contain a numeric range of values, enter the range in the Allowable Values dialog box and choose Hard Code so that Designer will generate a check constraint.
- For primary key values, consider letting the database assign them by using a default value of SYS_GUID(). This guarantees that your primary key will be unique within your table, across tables, and even across different databases. If you are building a distributed system, this step is essential.
Workflow
After a round of modeling, I need to translate the Designer model into a database schema. The database model in Designer is complete enough that I use it as the "point of truth" in my workflow. Designer generates the actual data definition language (DDL), which I then save in a source control system. Alternatively, I can use Designer to deploy the schema directly to the development database.
A powerful feature of Oracle Designer is the ability to compare the model with
a target database schema and output
just the DDL changes needed to bring
the schema in line with the model. In
my workflow, I make any changes needed to the schema in Designer first. Then I apply the changes to the development database and save the SQL scripts for patching other systems.
Test It
After my schema is deployed and running on a database, I test it and show the developers how it is to be used.
- Rather than leaving it to chance, I write PL/SQL stored procedures for any common transaction or any transaction involving more than a couple of steps.
- I write sample queries to test the performance and functionality of my schema. I create good sample data so that developers can get a better idea of how the schema is to be used.
- I write test scripts to check the integrity of the data and also check each of the data integrity mechanisms.
- I do a security review and write test scripts to check my security assumptions.
You've Paid for It
I strongly feel that you need to start any J2EE project with a solid database schema. Letting container-managed persistence or an object-relational framework use the database as a "bit bucket" is a critical mistake that can lead to slower performance, more code in the middle and client tiers, and increased time-to-market. You've paid for your database; why not exploit it?
Let me know your thoughts on this topic and more at devtrends.oracle.com.
Cameron O'Rourke ( devtrends.oracle.com) has been an Oracle technologist for more than 11 years.
| 
Bookmark
