Simplifying service interfaces - avoid database ids

Let's assume you have a data-centric (i.e. CRUD) service for retrieving product information. For the sake of simplicity, let's assume an XSD that looks like this:

<complexType name="product">
<sequence>
<element name="sku" type="string"/>
<element name="name" type="string"/>
<element name="description" type="string" minOccurs="0"/>
<element name="product-type" type="product-type"/>
</sequence>
</complexType>

<simpleType name="product-type">
<restriction base="string">
<enumeration value="apparel"/>
<enumeration value="subscriptions"/>
<enumeration value="digital_video"/>
</restriction>
</simpleType>

I'm defining a simple product that includes a product-type enum value, so a sample document would look like:

<product>
<sku>SHG876SHFW</sku>
<name>Navy Blue T-Shirt</name>
<description>A nice, blue shirt for your enjoyment!</description>
<product-type>apparel</product-type>
</product>

We explicitly want to keep the service interface simple and free of how things are represented within the service - meaning, not exposing database ids where they have no apparent meaning to a client of the system. Exposing the id of an entity like product seems reasonable, but is unconvincing for items such as lookup values, which in my designs have a unique integer id as well as a unique, human-readable name. The names should be immediately understandable to clients rather than needing a secondary lookup on their own end. For example, using "apparel" is much easier to understand than "4".

These lookup values I tend to treat as enum values in XML/XSD, and in the database they get their own row within a lookup table.

By asking to clients to pass in both an id and a unique name is bound for disaster - this typically happens with auto-generated code/service interfaces. So, for the simplest and easiest way to integrate, and to hide more of the internal nuts and bolts, I just use enum values in the interface.

Running multiple instances of MySQL on the same machine

This may be documented elsewhere on the internet, but I thought I capture my own notes about how to setup and run multiple MySQL instances on the same box. I was playing with this mainly for experimenting with sharding, in particular with the Hibernate Shards project). I'm going to make several assumptions for this discussion: that the databases will, data excluded, be mirror of the other in terms of essential config and schema, that we're running on *nix (I'm using solaris x86), and that we're setting up two MySQL databases.

After the obligatory "download and install", create two directories that will hold all of the database files. For simplicity, I created these directories in the MySQL home directory. Next, and this is the most important part, create two separate MySQL configuration files, one for each database. You only need to specific the unique attributes per instance, which include the directory for files (datadir), socket, port, and the name of the pid file. Here's my first config file:

[mysqld]
datadir=/opt/csw/mysql5/shard0
port=3307
socket=/tmp/mysqld-shard0.sock
pid-file=/opt/csw/mysql5/shard0/pid

And here's my second:

[mysqld]
datadir=/opt/csw/mysql5/shard1
port=3308
socket=/tmp/mysqld-shard1.sock
pid-file=/opt/csw/mysql5/shard1/pid

As you can see, I've just tweaked the values slightly for the second instance.

The next thing to do is setup each database with the proper MySQL master tables and db files. As this is the standard way to setup a database instance, here's the command you need to run twice, one time for each insatnce:

<mysql_home>/bin/mysql_install_db --datadir=/opt/csw/mysql5/shard0
<mysql_home>/bin/mysql_install_db --datadir=/opt/csw/mysql5/shard1

All you need to do now is start up both instances, and you are good to go. The only special thing you need to when starting up is pass the name of the configuration file, like this:

<mysql_home>/bin/mysqld_safe --defaults-file=/opt/csw/mysql5/shard0/shard0.cnf

Note that, and this comes straight from the MySQL documentation, the defaults-file flag must be the first parameter on the command line.

And that's it!