On the topic of Grails Audit Logging Plugin version 0.5.4

Last night, I released a new version of my Audit Logging Plugin. Release notes are here. There had been an outstanding compatibility issue with Grails 1.3.x based on JUnit 4's Integration tests. The bug documented here has been open for several months as I haven't had time to come back to the project to work on it.

Normally I don't blog about tickets but I figure this one requires some attention because I've run into a few plugins that I need in my day job that are "broken" in 1.3.x and I'm not sure who else is having these troubles. In the interest of helping the community as a whole I want to cover my experience.

It turns out that GRAILSPLUGINS-2243 (titled "Integration tests with auditable domain class do not observe test transaction boundary") is actually a more accurate description of the actual bug. The symptom is during integration testing you can't delete entities that are audited. If you take the same code and run it in development or production mode you can delete the objects.

The issue with my code is in this block... (extraneous logging and debugging code removed)

Session session = sessionFactory.openSession()
def trans = session.beginTransaction()
def saved = session.merge(auditLogObject)
session.flush()
trans.commit()
session.close()

Keep in mind this block functions exactly as expected in Grails 1.2 under all environments I know of. The one environment this code is horribly wrong is Grails 1.3.x in Integration testing. Only at that time does this code cause a problem.

So let me ask you... what's wrong with this code?

Before addressing the real issue, questions I anticipate:

1. Why are you opening your own session?

   Because on a transactional database in some cases the audit log must fire after the database insert so that I can log the automatically assigned object Id. This means you can't use the open session without invalidating it.



2. Why are you using merge and not save?

   Because there are edge cases where the AuditLog object will be saved twice and I don't want to dictate the logic for that in this block to keep it simple



3. Why are you flushing the session?

   Because we want to make sure the audit log is being written outside the regular transaction cycle and this particular session is going to perform no other job we want to ensure the log is written out to the database

So ... what is wrong with this code again?

def trans = session.beginTransaction()

Apparently, under integration testing in Grails 1.3.x if you start a transaction in the way I have you will cause a problem with the other independent transactions in the rest of the application. This is simply not the behavior I see in my production systems under Grails 1.2 or in my staging systems running Grails 1.3 for that matter.

My solution is a bit of a work around. In the application context initialization for the Audit Logging plugin (the bit that runs just as you start up your grails application I have a block that does this...

if( Environment.getCurrent() != Environment.PRODUCTION && ConfigurationHolder.config.auditLog?.transactional == null ) {
       transactional = false
}

... which simply says in environments that are not production (Notice I'm using the Grails 1.1 API to find that breaking compatibility with Grails 1.0) the boolean flag "transactional" is set to false. I then wrapped the begin and commit calls to the transaction around a check on this boolean. For good measure, I gave the end developer the ability to override this behavior using the setting "auditLog.transactional" in their Config.groovy file. (It would figure that I would release this as a feature and someone needs to use those transactions in their integration testing because they run their testing environment against Oracle or something so here's an out for you to get the old behaviors)

What took so much time to find this bug? It's not clear cut basically. I stumbled on this answer by blindly trying different things until I found a combination that worked. Then I tried to build a theory around this result that fit the facts that I had on hand.

My theory is the JUnit 4 integration environment does something to the session factory so it does not really produce new sessions such that the session that I get in my plugin's listener code is not really a separate session or transaction. I call commit on this transaction and when the session is returned to the normal GORM code it is already committed so the actual entity delete call can't happen and is swallowed.

What is disturbing about this is I didn't invent this technique for Audit Logging I'm implementing here. I created this code after being educated by the Hibernate wiki. This means if my theory holds it's not just a Grails application that will observe this strange-ness ... pretty much any Hibernate application with this kind of Audit Logging will be affected when integration tested under JUnit 4.

Beyond just this anyone developing applications that open their own sessions independent of the GORM session may potentially see bugs during integration testing that do not manifest in other environments. That can be a very difficult situation to deal with. So I've over documented things here for the benefit of the community at large.

In other news...

In other places I've noticed a change in Groovy 1.6 and 1.7's builder API. In particular I use RichUI in one project which makes calls like...

builder.yield(" var ${attrs.yuiVariableName}DataSource = new YAHOO.util.XHRDataSource(\"${attrs?.action}\");\n", false)

... under Groovy 1.6 yeild was a builder method.

But, under Groovy 1.7 this method is not present. That means the HTML rendered by code that used the yield and yieldUnescaped calls now create tags <yield> and <yieldUnescaped> which breaks some pages (but not all).

So far these are the only surprising issues I've run into that has kept Grails 1.3.x off of my production servers. I haven't yet seen and great benefit to 1.3 so I can't say I'm deeply compelled to move to the new version.

I am looking to rewrite the plugin in light of GRAILS-5725 but this will be a breaking change. Frankly, it's such a large change I'm surprised it was not pushed to Grails 1.4 ... however, it does not appear to interfere with the operation of plugins using the listener injection technique I am also using so no harm no foul.

I see I've lost a whole star in my plugin ratings so, my apologies to my end users who have not had a plugin that works with your Grails 1.3 integration tests. I sincerely hope I can earn back that extra star from you. Thanks for your support and help. I am reviewing code submissions for another version of the plugin coming up and I greatly appreciate your support.

Never edit icky JBoss XML files again!

Do you have a JBoss application server? Do you have configuration files in that server that need to be edited or changed periodically? Do you have an allergy to XML?

Have I got a solution for you!

JBoss has the amazing ability to load *.properties files from any location (on the file system or on the internet) as part of its start up routine. So you can set properties in a simple properties file instead of an icky XML file.

NOTE: I'll refer to the directory you have installed JBoss into as $JBOSS_HOME you may have your JBoss server installed in /opt/jboss or /usr/local/jboss for example. It doesn't matter.

NOTE: I'll assume your JBoss is set up to load the "default" server directory. If it isn't then I think you are smart enough to figure out what to do on your own.

1. Stop the JBoss server


2. open the file $JBOSS_HOME/server/default/deploy/properties-service.xml in your favorite text editor.


3. Find the tag

   <mbean code="org.jboss.varia.property.SystemPropertiesService" 
     name="jboss:type=Service,name=SystemProperties">



4. Add this tag:
   

   <attribute name="URLList">
         /etc/jboss/system.properties
   </attribute>

   
   


5. Comment out or delete any other instances of the <attribute name="URLList"> ... </attribute> tag

Great! Now when we start up the JBoss server again it will read the file /etc/jboss/system.properties and set its properties from there. But, wait... the file doesn't exist yet. If I start my JBoss server and that file isn't easily readable it will crash.

So let's create the directory...

$ sudo mkdir -p /etc/jboss

... and the file ...

$ sudo touch /etc/jboss/system.properties

... now we can restart JBoss safely.

We don't get much benefit yet though. Nothing in JBoss is using that file. So let's take a database-ds.xml file and configure it to use system.properties for its connector-url, user-name, and password.

Assuming you have a file like $JBOSS_HOME/server/default/deploy/database-ds.xml you can edit it to look like:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
   <local-tx-datasource>
      <jndi-name>MyDatabaseDS</jndi-name>
      
      <connection-url>${MyDatabaseDS.connection-url}</connection-url>
      <driver-class>${MyDatabaseDS.driver-class}</driver-class>
      <user-name>${MyDatabaseDS.user-name}</user-name>
      <password>${MyDatabaseDS.password}</password>
      
      <min-pool-size>10</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <prepared-statement-cache-size>10</prepared-statement-cache-size>      
   </local-tx-datasource>
</datasources>

Now edit /etc/jboss/system.properties to hold those values... it should look like this

# config for database-ds.xml
MyDatabaseDS.connection-url=jdbc:postgresql://localhost:5432/database
MyDatabaseDS.driver-class=org.postgresql.Driver
MyDatabaseDS.user-name=username
MyDatabaseDS.password=password
# this example is for postgresql but you could substitute any database.

... when you start up JBoss it will read the /etc/jboss/system.properties file and when it looks at database-ds.xml it will see the ${variable} values and substitute the values you set in system.properties for the ${variable}. If a ${variable} is not present, there will be no substitution... so be sure that every time you use a variable you set its value somewhere.

You can also set system wide system properties this way too. Commonly on servers that I work with the unix host name has nothing to do with the hostname being served. So I commonly set:

server.host=server.example.com

For use in my Grails applications which I then read in Config.groovy like this:

grails.serverURL = "https://${System.getProperty('server.host')}/${appName}"

... but that is another story ...

Data validation teirs

Data is either valid or invalid right? Well, sort of. I'm working with medical data like the Blood CHEM-7 test. This data has the possibility of being valid and being abnormal. So there is this concept of valid capture data and data that is out of range.

For example here's the CHEM-7 data definition in English:

• BUN: 7 to 20 mg/dL


• CO2 (carbon dioxide): 20 to 29 mmol/L


• Creatinine: 0.8 to 1.4 mg/dL


• Glucose: 64 to 128 mg/dL


• Serum chloride: 101 to 111 mmol/L


• Serum potassium: 3.7 to 5.2 mEq/L


• Serum sodium: 136 to 144 mEq/L

I might code this as an Object and write validators to make sure that the saved data is in range. My Groovy object for GORM might look like this:

class Chem7 {
   static belongsTo = [patient:Patient]
   Integer bun
   Integer co2
   Float creatinine
   Integer glucose
   Integer serumChloride
   Float serumPotassium
   Integer serumSodium

   static constraints = {
      bun(nullable:false,range:7..20)
      co2(nullable:false,range:20..29)
      creatinine(nullable:false,range:0.8..1.4)
      glucose(nullable:false,range:64..128)
      serumChloride(nullable:false,range:101..111)
      serumPotassium(nullable:false,range:3.7..5.2)
      serumSodium(nullable:false,range:136..144)
   }
}

... but I've got a problem. The very reason that we conduct the CHEM-7 blood test is to find irregularities. The constraints that I've expressed only detail the normal range of data for a CHEM-7... so what should I do?

If I make the table definition in the database very generic. Allowing the table to accept NULL values or numbers of any kind for each of these values then I can do this with a chem7 object:

   chem7.save(validate:false)

... as long as the table underneath the Grails application can accept the data then I'll save my irregular chem7 test results. This would mean that anyone who was editing the chem7 would get a warning about how it was not a normal test result. Yet no matter how irregular the test was we could still save the results and log who entered them.

So imagine a "Forced Save" button next to the normal save button on your Grails application. Simple enough to write. But it allows us to use validation in a tiered fashion. Validation at the controller and validation at the database. The important thing to keep in mind is that the validation must be weakest at the database level.

Grails, two databases, and you

I'm working a project right now that requires me to access a legacy LAMP system and then import these into GORM/JPA persisted objects. Douglas Fils has some good notes on this topic in his post "grails datasource in resource.xml." So I'm not going to cover how to get access to a second database in detail since it doesn't really need covering.

I will point out that if you are thinking you'll run two GORM instances... persisting one group of domain objects to one database and persisting another set of object to a different database... well, I haven't figured that out yet. What I will talk about is one database connection that uses groovy.sql.Sql to create simple fast DAO.

What I've been doing lately is writing queries against my old database that "normalizes" the result sets into exactly the properties I have in my Grails domain classes. For example let's say we have a Person table on our legacy (non GORM) database that looks like this:

+------------------------+--------------+------+-----+-------------------+-------+
| Field                  | Type         | Null | Key | Default           | Extra |
+------------------------+--------------+------+-----+-------------------+-------+
| PersonID               | int(11)      | NO   | PRI | 0                 |       |
| FirstName              | varchar(255) | NO   |     |                   |       |
| MIorMiddleName         | varchar(255) | NO   |     |                   |       |
| LastName               | varchar(255) | NO   |     |                   |       |
| EmailAddress           | varchar(255) | NO   |     |                   |       |
| Gender                 | varchar(255) | NO   |     |                   |       |
| DataIDontCareAbout1    | varchar(255) | NO   |     |                   |       |
| DataIDontCareAbout2    | varchar(255) | NO   |     |                   |       |
| DataIDontCareAbout3    | varchar(255) | NO   |     |                   |       |
| DataIDontCareAbout4    | varchar(255) | NO   |     |                   |       |
+------------------------+--------------+------+-----+-------------------+-------+

Some of these columns we'll ignore... some we want to pull out and use in a new layout that better normalizes the data for our use. In my example to keep things simple let's say we decided to pull the name attributes out as a separate object and table. There are other cases that can result in rather complex pivot queries but I'll leave those for another time... suffice to say you can put a lot of work into the queries.

So in my MySQL queries I have something like this:

-- person query
  SELECT PersonID AS 'id'
    , EmailAddress AS 'email'
    , SUBSTR(Gender,1,1) AS 'genderCode'
  FROM PeopleContact
  WHERE 
       EmailAddress = ?

... and ...

-- name query
  SELECT FirstName AS 'firstName
    , MIorMiddleName AS 'middleName'
    , LastName AS 'lastName'
  FROM PeopleContact
  WHERE 
       PersonID = ?

These queries now match their result columns with the properties of the classes I have in my Grails domain ...

class Person {
  Long id
  Long version

  Name name
  String email
  String genderCode
  static constraints = {
     name(nullable:true)
     email(nullable:false,email:true)
     genderCode(nullable:true,blank:true,inList:['M','F','U'])
  }
}

... and ...

class Name {
  Long id
  Long version

  static belongsTo = ['person':Person]

  String firstName
  String middleName
  String lastName
}

Side NOTE: This particular normalization trick is interesting... I'm not necessarily recommending it... two people with the same name could end up pointing to the same name object. We would have to write our services & controllers to be sure that changing the name of one person did not change it for both... this is a point of debate with our system designers right now. However, we are doing something similar with mailing addresses which are currently in-line in several tables. Everyone agrees that normalizing addresses will make managing mailing addresses easier in our case.

To help with putting query data into objects I've written a mapping method that uses features of the GroovyRowResult class to our advantage:

    /**
     * A generic mapping utility assumes that the row
     * has keys that map one-for-one onto the object.
     * 
     * This is useful only if the queries are specially
     * constructed so they can be mapped.
     */
    public static void map(obj,row) {
      row.keySet().each({ key ->

           if( obj.properties.containsKey(key) ) {
               obj.properties[key] = row.get(key)   
           }

      })
    }

...this is where working with groovy really shines!

Now that I've laid this ground work, in a service that has a groovy.sql.Sql object I've referred to as db. I've loaded the queries into strings and now I'll take the db object and pass it the queries and a parameter to go where the question mark is... in our example first we will query for a person based on their email...

 def person = new Person()
 def personRow = db.firstRow(personQuery,[email])
 map(person,personRow)

... and for the name...

 person.name = new Name()
 def nameRow = db.firstRow(nameQuery,[personRow.id])
 map(person.name,nameRow)

Now I've got a person object and a name object. To save them to the database now I just call the Gorm save commands on them... I can work with validation and I can work with all the other nifty Grails tools at hand. That means you could use the DAO snippets from a controller with a special "import" action that then forwarded to a "create" based action.

So we've seen code fragments that can be used to create an import DAO that can map rows from straight SQL queries onto GORM persisted objects. You can imagine that reversing the process would be very easy using the same tricks in reverse with update statements. That means hooking up a Grails project to import and export to a LAMP database can be very easy.

I'd love to know if anyone else is trying this and what their observations are or if they have better ideas.

Grails Datasource, legacy database, and you

Any one out there trying to work with Groovy, Grails, and legacy databases?

Well, I am and I thought I'd share with you my final dataSource configuration. I'm using a legacy database that drove an Apache + mod_perl web site that had a MySQL 3.x database. The Groovy/Grails project I'm working on will interact with that data (right now as a read-only service) and here's the final dataSource definition I ended up using for our first beta.

import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration
/**
 * This project is driven off of Legacy data.
 * Use update to prevent wiping old tables...
 * much of the data is accessed using JPA classes generated by
 * the Hibernate3 reverse engineer tool.
 ********************************************************************/
 dataSource {
  configClass = GrailsAnnotationConfiguration.class
  dbCreate = "update"
  dialect= org.hibernate.dialect.MySQLMyISAMDialect // or perhaps MySQL5Dialect
  pooled = false
 }
 environments {
  development {
   dataSource {
    driverClassName = "com.mysql.jdbc.Driver"
    url = "jdbc:mysql://localhost:3306/legacy?zeroDateTimeBehavior=convertToNull"
    username = "legacy"
    password = "legacy"
   }
  }
  test {
   dataSource {
    driverClassName = "com.mysql.jdbc.Driver"
    url = "jdbc:mysql://localhost:3306/legacy?zeroDateTimeBehavior=convertToNull"
    username = "legacy"
    password = "legacy"
   }
  }
  production {
   dataSource {
    jndiName = "java:/LegacyDS"
   }
  }
}

The LAMP stack was upgraded to use MySQL 5 recently so we can set up our new projects using the setting dialect= org.hibernate.dialect.MySQLInnoDBDialect which will give us the ability to use actual foreign key relationships. That's a big deal if you are a LAMP developer. I suppose that in short order I will be a Grails, Linux, Apache, MySQL developer... a GLAM developer... ah... no... lets not call it that.

BTW: I deploy to a JBoss Application server in production which manages the DataSources in a legacy-ds.xml file under /usr/local/jboss/server/default/deploy that's why my production dataSource uses the jndi lookup.

Issues of Concurrency

I have a problem. I have two databases. One old one and one new one. The new one is fully normalized and the old one is not. Both databases drive their own front ends. One is a Perl front end the other is a Spring Java front end. The data between both interfaces has to stay in sync. Our DBA has elected to use triggers to keep the databases in sync.

Hibernate uses its own Version column to keep up with which version got committed to the database and when it happened. There are articles dealing with JPA, Hibernate, and Concurrency... my problem is a little different.

Here's my example of the problem.

Let's say you have a web application using an ORM engine like Hibernate. There is Entity version 1 in the database. User Alice checks out version 1. User Bob checks out version one.

Alice edits her copy of version 1 and sends it back. The ORM engine checks the object version against the database version and they match and on commit of Alice's changes the database version number is set to 2.

Bob makes changes to his copy of version 1 of the Entity and checks his version in. The ORM checks the database version and sees version 2 in the database. Version 1 is old and Bob gets an error reporting mid-air collision.

Now lets say you introduce a second database. This second database uses different Relational mappings but is itself an OLTP. Let's pretend both databases have exact copies of the data initially and an update trigger mechanism keeps the two OLTP's in sync.

If Alice checks out version 1 from the first database and the first interface and Bob checks out version 1 from the second database and the second interface. When Alice makes her change and creates first version 2 (2' or two prime) and Bob commits his changes to create second version 2 or (2'' or two double prime) the trigger from the first database to update the second will fire simultaneously as the trigger from the second.

In other words Alice's OLTP is unaware of changes made on Bob's OLTP and both changes are in route to the other's.

How should the two databases proceed?

Should last saved win? Should both databases error and roll back? Should the systems attempt to "merge" changes? Should one system be elected the "authority" for this data electing that Alice's interface and OLTP are always the right ones?

Database questions...

If you were building a new project today, which database should you use?

If you are working with JBoss how do you identify the need for an XA compliant database?

If you need XA and you are on a budget which database do you use?

Database Vendor Political Landscape as of 2007

Please note this is simply my opinion. Companies are presented in random order. No official recommendations are being made.

Oracle: Oracle the company has filed court proceedings against SAP citing that SAP has been stealing Oracle intellectual property. Oracle 11g is still closed source and proprietary. Considered the leader in RDBMS technologies and offers unique features such as XML Publisher and XML SQL Java API's that allow java developers to do ORM in Oracle proprietary ways.

Microsoft: Owner of both Access and SQL Server products, SQL server is based on Transact-SQL which is an implementation of the ANSI SQL standard. It is favored by .NET developers because of its tight integration with the .NET Framework. SQL Server runs only on the Windows operating system and as such can only be used in production environments that are predominantly windows. Security vulnerabilities such as the "SQL slammer worm" have scared some shops away from this option as an internet application OLTP store.

IBM: acquired Informix and supports Informix along side DB2. DB2 now has an open source version. Informix and DB2 have started to share features. IBM's move to open source DB2 may indicate a shift in IBM's software development and support strategy. The acquisition of Informix may indicate a desire at IBM to freshen their database offerings. Informix is actively supported by IBM and has had a new release this year.

Sybase: Still a traditional software shop, their still closed and proprietary products based on Transact-SQL have confusing names such as OpenServer and OpenClient which typically are used in industry to indicate Open Source or at least a reviewable source code base. This is simply not the case with the Sybase products. These products are "Try and Buy" products which has absolutely nothing to do with Open Source. However, Sybase still has a strong following but their popularity is waning.

Firebird: an open source RDBMS that features ANSI SQL 2003 compliance is based on Inprise code base from Borland's now defunct InterBase database product. This project is extremely young and unproven by RDBMS standards.

MySQL AG: In addition to the unique MySQL product which has earned acclaim and scorn alike for its design, MySQL AG acquired MaxDB from SAP. MaxDB is an ANSI SQL 1992 compliant product. MySQL itself is partially ANSI SQL 1999 compliant... or rather MySQL does not comply entirely with ANSI standards but enjoys a 10 million user install base. That large install base has earned it the title of "The Open Source Database" since it has become a defacto standard for open source projects such as Ruby on Rails.

Ingres Corporation: Still enjoys a following, (judging from all the comments below to fix this post), but has not enjoyed much market attention. The survival of the product and company appears to be driven by legacy support.

PostgreSQL: Sun Microsystems provides support for Postgres which is an ORDBMS (Object-Relational Database Management System) which is an open source project based on Ingres. It has a much richer feature set than MySQL and commercial technical support available from Sun Microsystems. Among the database players it is unique for its support of multiple PL/SQL sub languages. While Postgres has not enjoyed the success that MySQL has it is used by clients such as BASF, IMDB, Skype, TiVo, USPS, Sony, VeriSign, and the US department of labor. Postgres continues to thrive but has no marketing buzz around it... it has a solid reputation in the Open Source community as a "real" database. (EDIT: Sun does not own PosgreSQL, they merely support it.)

Apache: The Derby RDBMS is actually the IBM Cloudscape database also known as the Sun Microsystems Java DB. The project has only emerged from Apache incubator status in 2004 and is relatively unproven.

The upshot

Oracle and Microsoft still are industry leaders and seem to be relatively comfortable each in their own niche. There doesn't seem to be a serious contender that could knock down either of them.

MySQL has achieved nearly total dominance and is currently the un-assailable king of the Open Source database market but this dominance is built on a non-standard and non-compliant database system that has serious issues with ACID compliance and major missing ANSI SQL features.

Several players (strangely, IBM is a small player in the open source database market niche) are challenging MySQL's dominance by open sourcing already proven, stable, and compliant databases. Serious technical contender Postgres can not seem to get a solid marketing based footing that could propel it to the front. Apache Derby and Firebird may prove to be serious competitors in five years. No other current players seriously factor in to the short term picture.

A fringe player DB4O (Database for Objects) is an interesting new technology but will not likely be a serious commercial space contender in the near future. The product is only 3 years old in production but has prominent users such as BMW, Boeing, Bosh, Intel, Ricoh, and Seagate.

The Rules of Data Normalization

When Codd came down from the moutain top he held in his hands two stone tablets that had these rules graven upon them. One tablet contained the first three rules, the second tablet contained the other two. The rules are:

1. Eliminate Repeating Groups: Thou shalt not repeat the names of columns in a table. The key shall designate a unique row without repeats.
2. Eliminate Redundant Data: Thou shalt not repeat data in a table. The data in a table shall be unique for the whole of the key.
3. Eliminate Columns not dependent on the key: Thou shalt not put data in a table that relates to a column not in the key.
4. Isolate Independent Multiple Relationships: Thou shalt not put multiple relationships in one table unless they are related to each other
5. Isolate Semantically Related Multiple Relationships: Thou shalt not mingle semantically unrelated data in the same table.

The first three rules can be committed to memory using this saying: "The key, the whole key, and nothing but the key... so help me Codd."

Good database design will not number column names. Good database design will focus on what uniquely identifies data and not repeat the same data over and over again in the same table. Good database design will only relate columns to the key creating new tables for newly identified keys. Good database design will avoid master "relationship tables" that force all relationships to traverse them. Good database design will not mix un-related data just because they share the same key.

Thus saith Codd.

Woe unto those who do not heed the words of Codd for doom and swarms of Bugs will follow all those who fail to heed the wisdom of Codd.

Joy and prosperity will follow all who hear and obey the words of Codd for they will know the ever lasting peace of DataIntegrity.

Space-time tradeoff

My half of a chat conversation:

"In computer science, a space-time or time-memory tradeoff is a situation where the memory use can be reduced at the cost of slower program execution, or vice versa, the computation time can be reduced at the cost of increased memory use."
From: http://en.wikipedia.org/wiki/Space-time_tradeoff

... so in a very general and theoretical sense... if there was enough ram on your system, your OS should put everything in RAM to make things faster... not just because Hard Drives are slow... but because it is faster to look up the answer to 32 billion divided by 8 than it is to compute 32 billion divided by 8. The more time it takes to compute an answer the smarter it is to save that answer so you can look it up later.

More RAM makes software many many times faster.

However, Database design teaches the exact opposite: only save that which you can't compute, and save that information in the most terse compact form possible. Which is why database engines tend to do a lot of on-the-fly computation, caching, and indexing. The reason for this is that Databases are concerned with consistency and accuracy of data and that is easier to maintain for terse, tight, inter-related groupings of data without any redundancy.

Database Design

Let's say I want a screen that displays a list of MP3s with Genre, Artist, Album, Song Name, Play Length. What would the database table for this look like? Many neophytes would design a table that looked like:

MP3Files
Column	Type	default
MP3ID	int	auto increment
Genre	varchar(255)	NULL
Artist	varchar(255)	NULL
Album	varchar(255)	NULL
Song Name	varchar(255)	NULL
Play Length	varchar(255)	NULL

... and amazingly never see the problem with this.