Elements of an Enterprise-Grade Web Framework – Part 2: Existing Database Schemas

A powerful database schema is one of the key components of an enterprise application.  Using the database to enforce business logic and ensure data integrity enables both flexibility and scalability in application design.  By coding business logic in both the database layer and application layer, the database has the flexibility to then also be used by other applications and processes without fear of corrupting data.  In addition, other systems can take advantage of stored procedures or views that make future development easier, instead of the platform lock-in created by storing all business logic in the application itself.

An enterprise-grade framework needs interoperability with existing schemas built-in to its DNA.  As such, parameterized database queries should be fast and easy to execute, without loads of syntactic fluff.  Let’s take as an example parameterized database queries in a few different web languages:

PHP


$mysqli = new mysqli('server', 'user', 'pass', 'db');
$stmt = $mysqli->prepare("INSERT INTO AUTHORS VALUES (?, ?)");
$stmt->bind_param('ss', $firstname, $lastname);
$stmt->execute();
$stmt->close();
$mysqli->close();


ASP.NET

using (SqlConnection con = new SqlConnection(constring))
{
SqlCommand cmd = new SqlCommand("INSERT INTO AUTHORS VALUES (@firstname, @lastname)", con);
cmd.Parameters.AddWithValue("@firstname", firstname);
cmd.Parameters.AddWithValue("@lastname", lastname);
con.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
}

Ruby + Django

Good luck integrating a complex external schema.
 |\\\\\\\|
 | _   _ |
( (o) (o) )
 |  . .  |
  \  _  /
   \___/

Python

db = MySQLdb.connect("server","user","pass","db" )
cursor = db.cursor()
cursor.execute("INSERT INTO AUTHORS VALUES (%s, %s)",(firstname,lastname))
cursor.close
db.close()

From these examples, the most fluff is generated by ASP.NET.  In addition to creating the connection, ASP.NET also requires an Open command.  Each parameter must be instantiated on a separate line, and if the undocumented auto-destruction does not properly close the connection, it will result in a possible memory leak.

PHP and Python have the most straightforward database interaction, though they still require three to four lines of code to execute the command.  A proper enterprise framework should execute database commands in one line, and simplify data reading and type conversion.  Since database access is the heart and soul of most web development, it should be done through functions that ensure minimal syntactic overhead.  Programmers should focus on logic and architecture, instead of fighting against language idiosyncrasy.

In the next section, we will take a look at a simple but often overlooked element of enterprise app development – the dynamic drop-down list of values from the database.

Written by Andrew Palczewski

About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.
Google+