Database Templates with MySQL

Once you've built several MySQL databases, you'll learn some shortcuts to database design.

Many databases are very similar. When creating new databases, developers
often build the same basic tables with only slightly different names and some
adjustments to columns. Rather than starting from scratch when putting together a
new database, developers will sometimes copy tables from an existing database,
give them new names, and then make modifications. This can be a big timesaver.

Why stop there? Take this trick a step further and put together a generic
database with a set of empty, standard tables. With a well-designed MySQL
template, you can quickly assemble the basics of any database as needed. A
template also allows you to focus on the more interesting aspects of a database
project.

In this article I will work through the process of creating a MySQL template
and then apply the template to a scenario. Your clients' needs will dictate
how a particular template might come together. However, in this article I will
assume that we primarily develop databases for businesses that sell tangible
products.

Core Tables

Databases for businesses that sell products typically consist of a core table with product descriptions and other product information. This is
basically their catalog. For our template, we'll create a database called
product_template with a core table named products.
Let's create it with some basic, minimal columns:

Regardless of the kinds of products our clients stock, we can probably
use all of these columns. Every product needs a unique number, so
rec_id is the primary key. It's not always necessary to record
the creation date, but it may prove useful in many cases so we've set up
a column for it. Since each product needs a product name, we have a column
called name. The columns category and
sub_category can be handy in grouping and sub-grouping products.
Their value, though, is debatable and limited.

I have found the status column to be particularly beneficial
for all my tables. It allows me to remove a record temporarily from the
user's view without having to delete it. Almost all of my SELECT
statements contain WHERE clauses that filter out inactive records.
Therefore, I just change the value of a record's status column to
in (for inactive) to eliminate it from my result sets.

Of course, you may find it clearer to change the column to
active with a Boolean type. This would let you select rows where
the active status is true.

We could add several other basic columns to the products table.
It's also missing columns that a particular client may need. However, for our
purposes here and as a template these few columns are sufficient. Later in
this article, after we've finished designing our template, we'll modify the
table for a fictitious client to see how to apply a template.

Count Tables

Although the products table can contain a good deal of
information on a client's merchandise, it should not contain a column for
inventory quantities. We'll use another table called inventory.
By having the inventory data in a separate table, we can have a separate row
for the inventory of each item for each store location. The two tables would
then be linked together by a key field.

Several columns in this table have the same name as products.
For some developers this can be confusing. For me, however, I like knowing the
names of common columns without having to look again at a table's description.
By reusing column names I can count on the key field of each of my tables as being rec_id, the record description column being
description, and the status column being status.

Besides the standard columns, this count table includes the
product_id, which links to rec_id in
products in SQL queries. This is the first column to which we're
giving a specific name. We use an identifying label here to reduce confusion,
so that we know to which table the column will link. Of course we could use
the same label for the key field in the main table in which it links (for
example, product_id in products instead of
rec_id), but that's not necessary. We know in the
products table, for instance, that the primary key is the
product's identification number. Giving the key field a generic, reused name
is partly a matter of style and partly a preference.

The next extra column above, quantity, stores quantity values.
The final column is store_id. It links the data to yet another
table, a reference table containing data on the client's store locations.

Reference Tables

To save time on typing and to speed up a database, many developers use
reference tables for storing otherwise redundant data. Reference tables can
hold data such as employee names, customer information, postal codes, or store
locations. For our template we'll set up two minimal, generic reference tables.
First we'll create one for basic data without address information:

By now the format of our generic tables probably seems a bit dreary and very
familiar. However, it's this dullness that makes a template reusable and
adaptable. It's the familiarity that allows a developer to create SQL
statements intuitively and quickly.

For reference tables that will contain addresses, we need a table called
addresses to contain many of the common columns:

This table has two name columns: one for the person's first name and
another for the last name -- again, rename these columns to your preference.
This address table is probably more suited for people rather than businesses,
so you may want to create another table for businesses.

Depending on the client's needs, we will use the generic tables above to
create several reference tables of different names and uses. When making
copies, we could rename some of them, but I prefer to leave these columns
unchanged. Invariably we will need to add more columns for specific reference
tables. We'll see examples of this in the next section when we use our
template.

Deploying a Template

Once we have constructed our products-database template with a basic set of
generic tables, we can then deploy the template for developing databases for
clients. For the examples in this section we will assume that a bookstore
business with a few locations has contracted us to set up a database.

The first step is to copy the template to a new database. There are many
methods and utilities we could use, but we'll create a new database
bookstore and then copy the tables from
product_template to the products table in the new
database using a CREATE TABLE...SELECT statement.

Although this simple method of duplicating the needed generic table will
work, it has two basic flaws: First, it copies the data along with the table
layout. This shouldn't be a problem if we're copying a table that's part of
our template: it won't contain any data. If it did, then a simple
DELETE statement could clear out the data in the new table.

The second problem is with the primary key, rec_id. The method
above doesn't duplicate the index, and the column does not have auto-increment enabled. The rec_id column is created, but it's not indexed and
the auto_increment isn't there. We could just alter the new
table:

The output shown here is without the table formatting lines; this is at the
core of the results. We would copy this text (the CREATE TABLE...
section) into a text editor and then change the table name from
products to books. Next we would paste it back into
the mysql client to create a new table with the proper settings
and without the data. Of course, if we needed to do this more than once, it
would be worth scripting.

Once we have the basics of books established, we can then
change the table for the client's specific needs. The products table for a
bookstore will require several additional columns. Also, books
needs to link to inventory and a few reference tables. We'll have
to alter the table to add the needed columns:

Based on the columns we've added to products, we will need to
make copies of the reference tables: two copies of reference for
authors and genres (plays and novels, for example) and a copy of
addresses for publisher data. We can copy all three tables
without adding very many columns to them.

The result of this exercise has been to show that in just a few minutes you
can have a good starting point for a new client database. You have your
products table, your inventory table, and various reference tables that all
link to the main tables. All you need to do is to find out any additional
columns that the client needs and add them to the appropriate tables. This can
be quite impressive.

A Collection of Templates

The database examples given in this article were for clients that run a
product sales business. Although it may easily be modifiable for various other
types of clients, in time you may want to develop a set of templates for
several different client types. It's not necessary to think ahead too much.

As you find yourself putting together a database for a new type of client,
keep in mind which columns might be generic (and label them accordingly), and
which columns are client specific. When you've finished the primary tables,
make a copy of the database and strip out the extra client columns. Then add
the new template to your toolbox for future use. In time you will be ready to
set up any type of database and will be able to keep your workload manageable when taking on a new project.

Russell Dyer
has worked full-time for several years as a free-lance writer
of computer articles, primarily on MySQL.