Internetworkers: http://www.ibiblio.org/internetworkers/

["Michael D. Thomas" <mdthomas AT mindspring.com>]

> -----Original Message-----
> From: internetworkers-bounces AT lists.ibiblio.org
[mailto:internetworkers-
> bounces AT lists.ibiblio.org] On Behalf Of Steven Champeon


> > Anyway.... I found some technical inaccuracies in this post.
> 
> Technical inaccuracies in a string of metaphors? Wow. That's pretty
> impressive, Michael. Can you also find abuses of poetic license in a
> hex dump? ;)

Well, since you asked, I'll tell you.

Don't kid yourself. Metaphors about technologies /can/ be inaccurate and
misleading. Promoting poor technology metaphors is, at best, naive, and,
at worst, irresponsible, dangerous and destructive. Belittling this
reality won't change it.

I can argue that the most important part of software technology is the
definition of good metaphors. Take the table in the relation model -
it's a metaphor for the electronic storage and retrieval of like data.
These software metaphors are all around us. Memory, threads, file,
folder, desktop, etc. 

Object-oriented languages and data models give us a powerful basis for
defining our own metaphors by creating a domain-specific vocabulary.
When I architect a big system, the object and data models that I create
are essentially metaphors and act as a base for explanatory metaphors.
The business folks look at the models and metaphors and make sure that
it correctly describes their business domain. The technology folks take
technology metaphors -- tables, objects, procedures -- and apply them to
the domain metaphors. Projects can easily fail if those models and
metaphors aren't well-defined and understood. In fact, I'll proffer that
any large software project that doesn't have good metaphors can only
succeed by accident.

This guitar metaphor implies a use of SQL's INSERT that would return an
error for most tables out there -- specifically, any SQL table that
defines a primary key or uniqueness constraint. If you don't have such a
constraint -- which is poor form -- then the implied use of the INSERT
statement will make a big mess. You'll end up making a copy of your row
every time you modify a value of that row. I don't think you can
construct a universe in which this is a good idea or desired behavior.

The implied use of the CREATE TABLE statement defeats the purpose of the
relational model, would perform very poorly and would make application
development very, very tedious.

As for hex dumps -- they aren't generally read by people. Thus, I
wouldn't analyze hex dumps in the same way that I analyze technical
writing. 

> 
> > To play along with this example, each row in our table would
represent a
> > guitar.
> 
> No - the key point I was trying to make was that learning databases
was
> more like learning a guitar where even the most mediocre beginner can
> bang out a simple tune, but years of practice can make you reasonably
> good, and many years of deep experience may give you skills and
insights
> not found in the average yeoman. That's all. 

I think we can do better.

Drywall takes years of practice. I don't see databases the same way. 

I think that your best path to database mastery is to study first. Then
you can avoid learning a lot of things the hard way through experience. 

But I should disclose that I do database training professionally -- I
just taught an "XML & Databases" class last week -- so maybe I'm biased.

Here's the way I'd advise a newbie to approach the SQL database learning
curve. I've written this so that you get the most important stuff first.
Towards the end you develop the master level skills. I've noted a couple
places where you could jump off based on your own needs and goals.

* First, clear your head of everything you've heard about SQL.

* Learn the relational model -- Codd's Rules are a good place to start.
(Dr. E. F. Codd developed the relational model in 1970.)
 
http://www.itworld.com/nl/db_mgr/05072001/
http://www.ncb.ernet.in/education/modules/dbms/general/CoddsRules.htm
http://www.mit.edu/people/tjw/Codds%20Rules.htm
http://www.aisintl.com/case/library/Date_Birth%20of%20the%20Relational%2
0Model-1.html

* Learn about Entity Relationship Diagrams (ERDs). You draw ERDs to
illustrate the relationships of your data. There are some other ways to
draw data out there, but ERDs are one of the most popular.

http://www.smartdraw.com/resources/centers/software/erd.htm
http://www.nmt.edu/tcc/sa/erd_how.html

* Time to figure out the basic admin stuff about your db server. How to
start it, how to stop it, manage users, roles, privileges, etc. It's
also a good time to do a little study on the basic architecture of your
chosen server. You might be able to avoid some gotchas down the road.

* Now start on SQL. SQL isn't completely standard across databases, so
it's best to refer to the documentation for your server. However, it's
good to know how to write portable SQL. Essentially, there is a large
subset of SQL usage that should be portable across servers.

http://php.weblogs.com/portable_sql

Different databases have different tricks that only work on that
database. They can make life easier, but then use of proprietary db
features lock you in to that database.

* Create some tables, do some inserts, run some queries, do some
updates, do some deletes, repeat. A simple inventory schema (product,
vendor, order, customer) is good to start with, as well as an employee
schema (employee, department, job). You'll have some many-to-many
relationships out there. Don't let them scare you. Just create another
table, give it some arbitrary name -- e.g., product_vendor_join -- and
stick it in between the tables. You'll have one foreign key that points
to the primary key in product, and one foreign key that points to a
primary key in vendor. 

* Learn how to write SQL scripts. When you are developing your db,
you'll probably blow it away a few times. Using scripts make it easier
to recreate the db and load data.

* Now you should figure out how to lay out the plumbing for your
application. E.g., JDBC for Java, ADO for .NET, PHP database functions,
etc. You should also understand how to make your application database
independent -- just in case you need to take your app from mySQL to
Oracle or some such. An example of how to do this is the Data Access
Object (DAO) design pattern for J2EE:

http://java.sun.com/blueprints/patterns/DAO.html

Of course, whether or not you make your app db independent depends on
your own requirements, cost-benefit analysis, etc. You should definitely
set up a mechanism so that you can capture the performance of all of
your queries in a log file. This will tell you where you might need to
denormalize.

* At this point, you'll need to study your problem and come up with the
data model. This can be a real test of communication skills. The key is
to capture the relationships between different entities. You'll need to
go talk to some subject matter experts and drive them crazy with
questions like, "So, does the milk in a carton come from just one cow,
or could it possibly come from several cows?"

This can be really grueling work, but it is very important. One thing
that makes it hard is that you are often talking to your customer.
They'll get tired after a while and you'll be inclined to give up.
Remember that the data model for their business domain is in their head.
Your job is to get that domain out and in to a diagram. If you don't get
that accurate model, then the database schema won't accurately reflect
the domain. Hilarity will ensue.

If you are lucky enough to have a requirements document, go through and
underline the nouns. A lot of those will end up being either tables or
columns in your database. But you should do an ERD first, even if it is
just on the back of a cocktail napkin or on a colleague's tummy.

#### At this point, you should be able to put some really nice simple
applications together. The next few steps are going to really help you
for more complex applications, large scale development, performance and
troubleshooting.
####

* I discussed indexes in the last email. This would be a good time to
learn the different kinds of indexes your db offers and how to use them.

* Study normalization. I talked about that briefly in my last email.
When your db is normalized, any particular value should only occur once
in your db. Remember that you may have to denormalize for performance
reasons after you start developing your db:

http://www.troubleshooters.com/littstip/ltnorm.html
http://www.databasejournal.com/sqletc/article.php/1428511
http://databases.about.com/library/weekly/aa080501a.htm
http://www.dbdebunk.citymax.com/page/page/626988.htm

* Take a look at constraints, such as unique, not-null, primary key and
foreign key. By using constraints, your application won't be able to
write data to the db that violates your db model. This is hugely
important on big projects. (As a rule, I avoid 1-to-many foreign key
constraints, though. An overly constrained db is hard to work with and
you usually don't really need a 1-to-many.)

* This is a good time to review the three-valued logic of SQL -- the
inverse of true is false, but the inverse of null is null. This trips a
lot of people up.

* Learn about the different types of joins. You should already be doing
inner joins at this point -- those are the standard "WHERE emp.dept_id =
dept.dept_id" scenarios. Here's a nice article on joins.

http://www.tomjewett.com/dbdesign/dbdesign.php?page=jointypes.php&imgsiz
e=medium

I find self-joins particularly interesting. You can recursively define
relationships in the same table. For instance, in an employee table,
each employee can point at its manager, who is also an employee.

* Figure out all the backup & recovery stuff for your db, as well as
bulk import and export.

#### You should be pretty solid on databases now. The next few steps
address production databases, development efficiency and tuning.
####

* If you are going to get a lot of users, you need to understand
transactional and concurrency issues. For transactions, you need to
understand the ACID test:

http://www.wikipedia.org/wiki/ACID

When you write to the db, you do so inside of a transaction. Multiple
transactions may occur at the same time and act on the same data. The
key to understanding transactions and concurrency is to look at the
different isolation levels:

http://www.onjava.com/pub/a/onjava/2001/05/23/j2ee.html?page=2

All the isolation levels have pros and cons. You'll have to decide what
is best. The default for Oracle is read committed, I believe.
Serializable can get you in trouble because only one transaction can
read data at a time. The difference between the others is slight if you
don't have concurrent transactions. (Concurrent transactions mean two
transactions acting on the same data at the same time. Just b/c you have
concurrent user sessions doesn't mean you have concurrent transactions.)
However, dirty reads, phantom reads and non-repeatable reads can cause
some hard bugs if you aren't careful.

* Now it's time to look at locking. Your database locks down data, but
you need to think about how to lock data at the application level. For
most complex applications, the database's locking isn't going to be good
enough. The key is to understand the difference between optimistic and
pessimistic locking:

http://minnow.cc.gatech.edu/squeak/2634

* If you have the money, get a tool such as Embarcadero or Erwin that
allows you to manage your database schema with an ERD. (I'm not aware of
open source equivalents.) Basically, you'll be able to develop your db
visually. Also, you can print out a copy of the ERD and show it to the
business folks. This is much better than showing them SQL scripts with
lots of CREATE TABLE statements. 

* Your relational data model usually isn't the only way data is modeled
for an application. There are also object models and XML models.
Object-relational mappings and XML-relational mappings are hotly debated
topics these days. You should definitely understand the various
arguments and develop your own theories about why/how/when/where to map
between the different models.

* OLAP vs. OLTP. OLAP is Online Analytical Processing. OLTP is Online
Transactional Processing. A typical OLTP app is a database that supports
ATMs. OLAP is data warehousing -- it's the stuff they do on Monday night
football: "The Steelers have punted 3 times in the 2nd quarter. The last
time that they won a game where they punted 3 or more times in the 2nd
quarter was 1998." OLAP applications tend to have lots of ad hoc queries
and usually have to be highly denormalized. OLTP and OLAP place
different demands on your databases which you'll need to understand for
serious tuning.

* Now you should go back and look at the create/alter statements for
tables and indexes. These give you lots of tuning options. When your db
is in trouble and you need to flip a magic switch, there's a good chance
you'll find your magic switch in these statements.

* Physical storage. Databases write their data to disk. You have control
over how this is done. With Oracle, you can describe which tables should
be written to which disks and you can partition a table across disks.

* Most databases allow you to write stored procedures and triggers. This
is code that runs inside the database. As the name implies, triggers are
triggered when you modify data in a table. In Oracle, you can set them
up to fire at a variety of times -- e.g., before insert, after insert,
before update of a row, after delete, etc. Be careful though. The more
stored procedures and triggers you write the more dependent you become
on the underlying database.

* Object-relational databases are neat -- Oracle is one.
(Object-relational databases aren't the same thing as an
object-relational mapping.) In an object-relational database, you can
define your own types of columns and you can associate functionality
with the values of the type. Oracle uses the object-relational feature
to implement XMLType. You can store an XML document in the row right
along with structured data. Through PL/SQL function calls that you embed
in your SQL, you can do XPath queries against the document. It's pretty
cool. XPath for the semi-structured XML data, SQL for the structured
data. Here's an example:

SELECT e.poDoc.getClobval() AS poXML FROM po_xml_tab e WHERE
e.poDoc.existsNode('/PO[PNAME = "po_2"]') = 1;

(Oh yeah, this isn't the only way to mix queries on relational and XML
data. XQuery along with SQL/XML provides another mechanism which should
be vendor neutral. Another discussion.)

* Really big databases, parallelization for availability and/or
performance, distributed databases. All exciting topics.

* To finish up, you should read as much as you can about your particular
database. Look for vendor specific information on ways to tune like SQL
hints. Understand the different components of the architecture. Look at
the configuration files. Try to figure out what all of the settings
mean. That kind of stuff. Going through the same exercise on a second
type of database would probably really drive the concepts home.

> Hope I didn't confuse any
> other overly literal minded folks out there...

I hope you didn't confuse any other folks, too!

Cheers!

---------------------------------------------------
Michael D. Thomas -- Software Developer, Author

"Oracle XSQL" in bookstores now
"Practical J2EE Web Services" available Spring 2004