No, it’s not a bedtime story, is a serious system’s design concept and I’m amazing that people don’t know about this.
As I mentioned in If you don’t know your data, you don’t know your application I was doing a Java Code Review, and I found a clear case of a much simplier solution. How simple you ask?

Well, without completing the task 100%, I achieved in less then 1 day (and lets say for the argument 1 more day of work), what is being worked on by somebody else for a week, with an estimate of 2 more weeks to complete. So let’s add 50% to my estimate, that’s a total of 3 days verses 15 days. You do the math. and yes that was last week and that task is still being worked on the same way, even with reference to my working code. Not to mention the code is a similiar magnitude of simplicity, and simplicity means cost savings in support, people so quickly forget that.

So what is this RAT and CAT:

• RAT – Row At a Time
• CAT – Chunk At a Time

This concept is really rather simple, however it’s amazing how implementations never consider this. Let me give you a simple example.

Your importing some external data (a flat file of 5 columns, and is inturn will be stored in 2 tables in a normalised form). So the RAT way of doing things would entail:

• Using the language in question, open the file, read line by line, converting into object for reference.
• Now for each row read you
  • You get the values (A and B), and then do a select to see if this row already exists in one of the normalised tables (calling in X). If it does, you have the surrogate key, else you insert the row and get the surrogate key
  • Now you do the same with the next set of values (C, D, E) which reference the normalised values (A and B), inserting into Y)
  • And so on and so on.
  • Report exceptions line by line if found

For those now laughing, it’s not really funny, it’s sad that programs are written this way. For those that say, but that’s the only way, well welcome to a different, radical and hard to learn approach.

The Cat way of doing things would entail:

• Create a temporary table
• Bulk load the data into the temporary table via appropiate command, e.g. mysqlimport or LOAD DATA.
• Using one select, insert into X rows from temporary table that are not already present
• Using one select, insert into Y rows from the temporart table that are not present, joining to X to get the appropiate surrogate key
• Report exceptions via one select of data that wasn’t inserted due to some rules

It’s not rocket science people.

I should mention this is a simple example, and it’s not always possible to do CAT processing for all operations, but generally some portion of batch work can be, and the remaining must be done in a RAT way.

Updated
Some more recent articles including We need more CATs (2009) and The Art of Elimination (2010)

The art of data modelling is definitely lost on some [most] people, or they never found it, even though they think they did. Over dinner with good friend Morgan last night we were swapping present stories on the topic.

Morgan wrote recently about I want my 4 bytes back damn it., and interesting example storing an ISBN. Further reference can be found at Getting started with MySQL of a more impractical ISBN example.

Disk is cheap now, so the attitude and poor excuse can be, well a few extra bytes doesn’t matter. Well no! If your a social hacker and have a website with a maximium concurrent connections of 2 maybe, but much like some recent Java Code Reviewing I just performed, just because the system isn’t 24×7, doesn’t give you excuse to be lazy about writing the code not to handle concurrency, thread safety and also as efficient as possible, in this case RAT verses CAT. (I’ll need to write about this, it seemed to go over some of the other professionals even)

I can remember a very specific example some 10 years ago in doing some performance analysis on a site. I’d identified the need for an additional index on a table. Now this table was sized for 200 million rows, and it already had about 70 million. The problem was adding another index required 4GB disk allocation. These things have an effect on sizing, growth and backups.

So the impact on appropiate sizing can clearly have an effect, if it was just one poorly sized column that’s acceptable (just), but normally it’s a pattern that litters a data model.

What’s important to realise is, it’s not just diskspace, it’s also memory. Without really touching on sizing data, I did mention some examples previously in Improving Open Source Databases – WordPress. Here the use of BIGINT(20) for primary keys proved my point. That’s 8 bytes, but unless you have going to have 4 billion categories, it’s a waste. It’s a waste when it’s a foreign key in a table, and it’s a big waste when it’s indexed, and that index is then in memory, and wasting more precious resources.

So how to do identify when the designer of the model has no idea about the intrinsic data value being stored? If you see tables with VARCHAR(255), that’s a clear sign. They have no idea regarding the data, so a default limit is used. Morgan referred to it as “Shooting guns in the dark with your eyes closed”. Books don’t help the cause, I was just skimming High Performance MySQL last night (one of the many freebies from the UC). There on page 82, is a table definition with not one column, but two with varchar(255). Hmmm!

If you see any new applications with VARHAR(255) they are even more lost, because MySQL 5, which has been around quite some time now, supports VARCHAR(65535). Speaking of that, has anybody seen VARCHAR(65535). I’d like to know.

Another example, is in Sheeri’s Top 8 SQL Best Practices Point 4 in regards to storing IP’s effeciently. If you log for example every page hit, this can be your largest table, and moving from varchar(15) to int can save you upto 11 bytes per row alone.

These may just be simple examples, but it’s the principle. When you define a column, consider it’s data, if you don’t know then take the time to do the reasearch and learn.

I’ve recently completed a contract and I’ve been in discussions with agents and other employers for further work. Having had one of the worse experiences in my previous work, I’ve been extra careful to ensure what I’m told at the interview/meeting stage is indeed true and accurate (in my last case it was not). I’ve also not made the assumption that an organisation that is dependent on software has placed a certain level of value on what’s in place. (in my last case I did, simply due to the size of the organisation and volume of business).

So, when being asked by people what I’m seeking, outside of the technical skills and compendencies, I’m seeking an organisation that places value on it’s existing software, it’s software quality, it’s software improvement and most importantly it’s software developers. It was unfortunate that for an organisation that lived in software, and would not survive long (especially at the present scale of operations) without it, they met none of these criteria. It was really sad, and overall I found the environment “depressing”.

Now “depressing” could be considered a harsh word, but it comes from both my ethos and also present circumstances in life. I had these two post-it notes on my wall at work, which summed up “What I was seeking/Where I wanted to be”, and “Where I was” in my job.

There have been a few articles recently, it was probably Marcus in Supporting ideas and being productive, based on earlier articles of How to Come Up With Ideas and How To Kill Good Ideas by Zack that motivated me to publish this. These articles have many good points, and while I could put my slant on it, I wanted to focus on just one thing.

In Software Development 10 years ago I was driven, now I know and I’m driven by passion. I always strive for something better then their presently is, seeking better quality, and better productivity, and most importantly today, simplicity.

Everybody is at a different position in life and it’s important to find the right fit. For me money is not a motivator, being productive, making what I’m doing better, where I’m working more enjoyable and productive, and who I’m working with a better place to be are important to me.

I’ve choose the word for the moment to best represent this as PRIDE.

Pride is:

• A sense of one’s own proper dignity or value; self-respect
• Pleasure or satisfaction taken in an achievement, possession, or association
• A cause or source of pleasure or satisfaction; the best of a group or class
• The most successful or thriving condition
• To indulge (oneself) in a feeling of pleasure or satisfaction

For me, I’m seeking an organisation that takes pride in it’s people, it’s products and it’s future direction, and that attracts people that have pride in what they do, they way they think and how they interact with others.

I’m playing with the latest 5.1.11 beta in a master/slave replication situation. Given a lack of H/W lying around I’m configuring a mixed H/W setup to leverage an existing office’s after hours CPUs running Windows XP for my slaves. So here is my test setup.

Server

Linux – 192.168.100.170

The following are the relevent my.cnf settings

[mysqld]
port=3307
server-id = 1
log-bin=mysql-bin

Confirming the server. I did some test commands prior to confirm the position.

$ mysql -uroot -p mysql
Your MySQL connection id is 47 to server version: 5.1.11-beta-log
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1759 |              |                  |
+------------------+----------+--------------+------------------+

Slave

Windows XP – 192.168.100.171

The following are the relevent my.cnf settings

server-id	= 11
master-host     = 192.168.100.170
master-user     = rep
master-password = dolphin
master-port     = 3307

Test

I’ve taken a few steps back for the documentation, to clearly identify any setup problems for those that are also reviewing this to setup replication.

Starting the Slave, I get the following error.

060720 16:36:22 [ERROR] Slave I/O thread: error connecting to master '[email protected]:3307':
  Error: 'Host '192.168.100.171' is not allowed to connect to this MySQL server'
  errno: 1130  retry-time: 60  retries: 86400

Makes sense, you need to give access to the slave to access the master. So to the uninitiated you may miss that a slave requires different access then a default ‘all’ for example.

On the master.

$ mysql -uroot -p mysql
mysql>  grant replication slave on *.* to rep@'192.168.100.%' identified by 'dolphin';

Just a side note, when defining the master replication you can specifically state databases to include or ignore, but to set up the appropiate grant on a particular database fails. For example:

mysql> grant replication slave on test.* to rep@'192.168.100.%' identified by 'dolphin';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

But the problem was not indeed here, looking closer at the error message, the host and the port matched the my.cnf file, but the user didn’t. It was ‘root’ when it should have been ‘rep’.

So, some advice later from the Planet MySQL #IRC (thanks Sheeri) you find if you RTFM at MySQL 5.1 Manual – Replication Startup Options.

So investigating the master.info file located in the slave data directory I get:

14
mysql-bin.000001
1839
192.168.100.170
root
dolphin
3307
60
0

There is a mismatch between the master.info file and the my.cnf file. I don’t recall the exact series of events when I setup the Windows XP installation, I know I first tested it as a master, and it’s most likely I then configured the slave settings, but used a master-user of ‘root’ initially and started the server. Then thinking this was insecure before I’d created the permissions, I changed the my.cnf file to use the user ‘rep’.

So the lessons today are:

1. Correctly configure your slave settings before starting the mysql server for the first time.
2. MySQL records slave replication authentication information in two files, the my.cnf and the master.info.
3. Making changes to my.cnf are not reflected in an existing master.info. In this situtation use the CHANGE MASTER TO command in addition.
4. Read the Manual. MySQL does have good documentation, and when it’s not quite right, like I found yesterday with Bug #21134, report it to help everybody in the future.

Now I fully agree this is a Replication 101 error. My excuse is the last replication I setup was in 2003 under 3.23, and it worked perfectly well in a production system for 2 years with a number of 3.23 and 4.0 slaves. Hence while I’m just refreshing my replication skills now.

For those that are using a Referential Integrity based Storage Engine in MySQL, the use of Foreign Keys can provide an extra level of data integrity within your application. There are of course caveats to this statement, such as correct design, proper transactional use etc, but that’s a topic for another time. The use of CASCADE has an effect on this.

So, just a quick refresher, using the MySQL Sakila Sample Database (if you don’t have it, get it. This can be a valuable tool in many other areas). Let’s first check out the Schema Diagram. We will look at the first relationship in the top left corner of the diagram.

There is a defined relationship with the actor and film_actor tables. An actor record can belong to one or more film_actor records, and a film_actor record must have one corresponding actor record.

Schema Definition

CREATE TABLE actor (
  actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (actor_id),
  KEY idx_actor_last_name (last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE film_actor (
  actor_id SMALLINT UNSIGNED NOT NULL,
  film_id SMALLINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (actor_id,film_id),
  KEY idx_fk_film_id (`film_id`),
  CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id)
         REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id)
         REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Explanation

The above shows the CREATE TABLE syntax as provided in Version 0.8 of the Sakila Sample Database. Looking specifically at the constraint we wish analyse.

  CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id)
         REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE,

The FOREIGN KEY for a specific column REFERENCES an appropiate table and column, and includes optional UPDATE and DELETE actions.

The ON DELETE RESTRICT is indeed optional as this is the default operation, but is a good practice for both readibility and future compatibility.
The ON UPDATE CASCADE in this case, is indeed unwarranted as the actor primary key is an AUTO_INCREMENT column and should never be updated. By good design definition, a PRIMARY KEY should never be updated in a table, and then should be set to RESTRICT. If however you ever wish to update a primary key value in RESTRICT mode, you can’t as to first UPDATE children before the parent is to set the value to an unknown value that values validation. The best approach here is always use AUTO_INCREMENT primary keys, and never update the values.

Use

So let’s take the current implementation for a spin to see how the default RESTRICTED implementation operates.

mysql> select count(*) from film_actor where actor_id=1;
+----------+
| count(*) |
+----------+
|       19 |
+----------+
1 row in set (0.05 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from actor where actor_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
    (`sakila/film_actor`, CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`)
   ON UPDATE CASCADE)
mysql> rollback;
Query OK, 0 rows affected (0.06 sec)

As you can see, an attempt to delete an actor fails due to a foreign key constraint failure.

The correct implementation for this design is to delete child relationships first. For example.

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from film_actor where actor_id=1;
Query OK, 19 rows affected (0.00 sec)
mysql> delete from actor where actor_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.06 sec)

Let’s now try CASCADE.

mysql> alter table film_actor drop foreign key fk_film_actor_actor;
Query OK, 5462 rows affected (0.78 sec)
Records: 5462  Duplicates: 0  Warnings: 0
mysql> alter table film_actor add CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON DELETE CASCADE;
Query OK, 5462 rows affected (0.69 sec)
Records: 5462  Duplicates: 0  Warnings: 0
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from film_actor where actor_id=1;
+----------+
| count(*) |
+----------+
|       19 |
+----------+
1 row in set (0.01 sec)
mysql> delete from actor where actor_id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select count(*) from film_actor where actor_id=1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

As you can see, by changing the definition to CASCADE, the requirement to delete all child rows is nullified.

Considerations

When designing I try to avoid the use of CASCADE. While it has it’s benefits, it is a “catch” by the database, and can hide poor design and coding techniques. In can however in more heavily loaded systems, provide a performance improvement, as additional commands for deleting don’t incur the network communications overhead. I’ll need to add this as a note to my performance testing guidelines to see what effect this has.

Another benefit on a more strict approach is MySQL for example currently does not fire triggers resulting on cascading constraint operations.

Try it yourself

As you can see it’s easy to use. To try it yourself.

• Use an appropiate storage engine, e.g. InnoDB. You can use ALTER TABLE [table] ENGINE=InnoDB; on your tables.
• Add an index to the target (foreign key) column, your source (primary key) will have an index by definition of being a primary key.
• Ensure your source and target columns are the same data type
• Create the appropiate constraint.

While the MySQL Manual refers to both the source (primary) and target (foreign) columns must have Indexes, I’ve found this not to be the case in 5.1. An ALTER TABLE will auto create the target (foreign) columns index if it doesn’t exist.

Just note, traps for young players. Both the source (primary) and target (foreign) columns must have the identical datatype definition. A common problem I’ve seen is that UNSIGNED is used for a Primary Key definition and not for a foreign key definition.

References
Wikipedia definition of “Referential Integrity”
MySQL Manual Page – FOREIGN KEY Constraints
MySQL Sakila Sample Database Forum
MySQL Forge Wiki on Sakila Sample Database