How to write DRY SQL in MySQL – Part 1: Views

Writing DRY code is a fundamental principle of software development.  DRY, or Don’t Repeat Yourself, was popularised by the 1999 Pragmatic Programmer coding practices book (the alternative being WET code, meaning Write Everything Twice, or possibly “We Enjoy Typing”).

DRY code is optimal, not just because it’s less typing, but because when you come to refactor code (make changes to it), it’s much easier if there’s a single place where that change needs to be made.  If you’ve repeated the same code blocks all over the place, you’ll need to go edit all of them.

The wider principle of DRY is, “Every piece of knowledge must have a single, unambiguous, authoritative representation within a system.”  Given that one of the fundamental design principles of good Relational Databases, Normalisation, is also about removing redundancy and repetition, you’d imagine that relational database would support DRY coding practices well.

Unfortunately, writing database server code (SQL queries, stored procedures, triggers, etc.) in a DRY way, is surprisingly hard in most Relational Databases I’ve come across.  So much so, that modern frameworks like Ruby on Rails and CakePHP tend to do far more of the database work than older systems.  But languages like Ruby and PHP aren’t set-based languages and they can be very inefficient and slow, especially if they have to make lots of round trips to the DB to get data.

There are several techniques though that anyone working with a database can learn to help DRY up DB code.

Use Views to DRY Common Joins

If your data tables are properly normalised (to at least 3rd or Boyce-Codd Normal Form which they absolutely should be) then you will no doubt have primary tables that store data linked to various ‘type tables’ which store category or attribute data for of entries in your primary tables.

Let’s say, for example, you have a table called books to store details of published and unpublished books.  Some attributes of books are many-to-one relationships, meaning a given attribute might have many entries but only one of them applies to each book.  Two examples of such attributes might be:

  1. publishers (let’s assume each book has just one publisher for this)
  2. publishing_states (a book has one state, “published”, or “unpublished”, or any other state we want to define, such as “work in progress”)

Attribute tables like these tend to be one the most common types of join that you need to write in your SQL queries and so these are perfect candidates for DRYing up.

You might also have a table called print_formats to store possible formats such as “Hard Cover”, “Kindle”, “Paperback”, etc. (a book may have several formats) so this is a one-to-many relationship.

Finally, of course, you’ll also store many-to-many relationships such as authors (a book can have many authors and an author can have many books).

Let’s say in your first iteration with a small sample of data your tables look like this:


id title publisher_id publishing_state_id author_id
1 The Looking Glass Club 1 1 1
2 Harry Potter 1 1 2


id name
1 Sencillo Press
2 Bloomsbury Press


id name
1 published
2 unpublished


id name
1 Gruff Davies
2 JK Rowling

Given these tables, you find yourself writing SQL code something like this all over the system.

select *
from books b
join publishers p on b.publisher_id =
join publishing_state ps on b.publishing_state_id =;

When you want authors and books you’d write:

select *
from books b
join publishers p on b.publisher_id =
join publishing_state ps on b.publishing_state_id =
join authors a on = b.author_id =;

You’ll no doubt be joining to other tables in different combinations as well as these, but the this triple join appears in all of them.  You’re repeating yourself, which is kind of a “bad code smell”, (which happens all the time in SQL, unfortunately).

These joins end up being used in your non-DB server code (PERL, PHP or RUBY) and stored procedures you write for efficiency, and inside queries that you use for administration and testing.  It’s everywhere already.

Now, let’s say you start adding some more book data, but this time you’ve been given some book titles that haven’t yet been published.  So now your books table is now:

id title publisher_id published_state_id author_id
1 The Looking Glass Club 1 1 1
2 Harry Potter 1 1 2
3 Supernova null 2 1
4 Wonderland null 2 1

You go to run the system and none of your standard queries bring back these any of these new titles.

You scratch your head for a while and then realise that when you did the first iteration, you only had published books in the system and it didn’t occur when writing your queries that unpublished books wouldn’t have a publisher_id yet.


All those triple joins are written as inner joins when the publisher join should have been a left join in order to bring back rows with no publisher.

You now have go search and edit every single query in the system and all your testing and admin code and fix the joins. You might be even forget your own admin code because you don’t run it except when needed and one day that will come back and bite you because you won’t spot the error.

Views to the rescue

As soon as you realise that you’re typing the same joins all the time (you usually know in advance with many-to-one attributes that you’ll be doing that), you can write a VIEW to do the join for you.

So, in iteration 1 of the system, (in MySQL) we’d have written this early on:

CREATE OR REPLACE ALGORITHM=MERGE VIEW books_publishers_publishing_states
select *
from books b
join publishers p on b.publisher_id =
join publishing_state ps on b.publishing_state_id =;

I’ve called the view the name of the tables concatenated. Yes it’s long, but it’s fully descriptive and it’s far shorter than writing the full triple join.

Very Important: MySQL has two types of view and you always want to use type MERGE where possible. MERGE views are just SQL statements and get merged at runtime with any query they are used in. If you don’t define the algorithm as MERGE, MySQL can decide to use the other type, TEMPTABLE which will evaluate the query, selecting all the data and then join the resulting temporary table with whatever data set your query returns. This could be A LOT OF DATA if your view has no WHERE clause like this. It can bring the whole system down.  Trust me, I’ve done it.

Now we have a VIEW, we can use it anywhere where we would before have written out the full triple join making our code lovely and DRY.

So we can write:

select *
from books_publishers_publishing_states;

or, our “all books and authors” query looks much simpler to:

select *
from books_publishers_publishing_states bpps
join authors a on = bpps.author_id;

Now, in this case, when we add our new data, the same problem occurs, but when we realise our join is wrong, we only have to edit it in one place.

We refactor the VIEW with the left join:

CREATE OR REPLACE ALGORITHM=MERGE VIEW books_publishers_publishing_states
select *
from books b
left join publishers p on b.publisher_id =
join publishing_state ps on b.publishing_state_id =;

Once we run this against the DB, everything works again with unpublished books.

Now, you might think this example is contrived (examples usually are), but we still save on typing and get to reuse code here. And I promise you will want to refactor your views as the system grows.

A more realistic example of such refactor is that you might want to add a where clause to filter certain data sets globally and change these quickly across the whole system. Views make this sort of refactor extremely quick to do.

DRY limits with MySQL VIEWs

Unfortunately, you can’t always create a view for every case. If you query uses a subquery in the where clause for example, MySQL can’t create a view for that case. Or, you might something more complex than a single query that you want to keep DRY. Fortunately, we can handle these cases another way.

In my next post, I’m going to show you a nice little hack that effectively lets you joins to the results of stored procedures as if they were tables or views.

The inability to treat stored procedure results like queries is another main source of WET database code.


How to add a WordPress Blog to a Moonfruit website

Moonfruit websites (a.k.a. Sitemaker) offer a rich set of very easy to use features for beginners. The built-in blog looks nice and is fully integrated with your site and design but it’s very limited. If you want real blogging power and you want to add WordPress to your moonfruit site, here’s how to do it.

NB: I will use Sitemaker and Moonfruit interchangeably here.

There are several ways you can add third-party widgets and services to moonfruit websites; they each have their pros and cons. Many third-party services offer little HTML snippets that you can simply paste into an HTML widget to get what you want. This works well for small things, YouTube videos and Twitter feeds for example (although there is now a widget for that). However, a blog is far more than a widget and whilst you can do this, it won’t look good or work well. (Technically speaking, they get rendered in an IFrame within the moonfruit HTML page and we don’t want this).

1. Set up an external WordPress Blog

There are two flavours of WordPress blog. You can have them hosted by WordPress or you can host your own. You cannot host your own WordPress blog on moonfruit’s servers, so that option isn’t available to you (unless you have your own server). I’ll assume for this that you already have a moonfruit site but you don’t have a WordPress blog yet. If you already have one, just skip the first few steps; you can use an existing blog with your moonfruit site.

  1. Create a WordPress account (or log in)
  2. Create a free WordPress blog (e.g. I created this as
    (You can start writing blog posts before linking to you moonfruit site if you want to. )

At this stage you should now have a separate moonfruit site and wordpress site. Now we need to link them.

Background information if you’re new to moonfruit: your site will be either on a free moonfruit domain such as (the ‘gruffiti2’ site name I’ve used here is a subdomain of, or you might want to use your own domain name, such as You can do this later. Note that your site can use multiple addresses and you set a primary one. This is configured in the admin section of the site editor.

2. Create a link page in Sitemaker

You can manually add links to your WordPress blog anywhere in your site but we want something that feels more integrated and that works with the menu that’s on your Page Master and appears on all pages.

When you create pages in your Sitemaker site, they get added to you menu automatically. You can create a special type of page that isn’t really a page but instead a link to an external site. This is exactly what we want.

  1. In the site editor., go to Pages and click ‘add a new page’
  2. Choose type ‘link’ from the page types offered
    Graphic choosing a page type for new pages in moonfruit sitemaker
  3. Set the link to be an external link and supply your blog address. Leave the ‘open in new window’ box unchecked if you want a more seamless link to the blog.
    Graphic how to add a page in moonfruit sitemaker
  4. Choose the page position (where the page will appear in the menu relative to other pages).
  5. Edit the name of the page to e.g. ‘Blog’ (the default name is the link you supplied which is ugly).
    Graphic how to rename a link page in moonfruit sitemaker

Your menu now has a link to your blog! Yay. At this stage your blog and your site will look nothing like each other. You’ll need to do a little work on making them visually alike (if you want to). However, your blog doesn’t yet link back to your moonfruit site. Let’s do that now.

3. Link your WordPress blog back to you moonfruit site.

WordPress gives you enormous control over look and feel and blog functionality, so there are actually many ways you can set up links back to your main site. I don’t recommend you try too hard to get the blog to look as if it’s part of your site. It’s a lot of work and if the site ever changes you have to do more work to keep them in sync. Instead, make the blog look visually ‘sympathetic’ with your moonfruit site. It’s okay if it’s not exactly the same; it’s a blog. It doesn’t have to have all the same menu options that appear in your moonfruit site. However, you do need it to have one main link back to your site that is obvious, so that a user can seamlessly navigate between the two.

We’re going add a WordPress menu to do this. I’m assuming you haven’t already added one. If you have, you’ll just need to add a link back.

NB: the default WordPress menu is not visible by default on your blog, but it exists. It has two items: a link to the blog itself called ‘Home’, and a link to an ‘About’ page for a bio describing yourself or your blog. We’re going to rename ‘Home’ to ‘Blog’ and we’re going then add a new ‘Home’ link back to your main moonfruit website.

  1. In your WordPress blog go to Appearance | Menus. The default menu should appear.
  2. Edit the Home’ menu item (click the little down arrow in the panel on the right) and change the name to ‘Blog’ (or whatever you called your blog menu item in Sitemaker, so that it matches.)
  3. In the left panel, click Links to open the ‘add link’ section. Enter the details of your moonfruit site address and call that ‘Home’.
  4. Click add to menu. It will appear under ‘About’
  5. Drag it to the top position.
  6. Click ‘save menu’.

Now we need to ensure the menu actually displays in your blog. At the top of the menu page there are two tabs. We’ve been using the edit menu tab. Click ‘Manage Locations’ to control where the menu appears in the blog.

  1. Under ‘Assigned menu’ if there is no menu selected, choose the menu you’ve edited or created as the Primary Menu. This makes the menu appear on the page.
  2. Save your changes.

That’s it! Write a test blog post if you don’t have one yet and preview it to make sure you can see the menu and make sure your links between the moonfruit site and your blog work correctly.

All that’s left to do now is bring the site visual styles closer together – if you want to. Choose a sympathetic blog theme and edit it if you want them to be even closer in look and feel. I don’t bother. I think it’s fine for you site and blog to look different, but it depends on your site.

Using a subdomain for better integration

We’ve linked WordPress to a moonfruit site and it works well, but you might be concerned that visitors will notice that it’s really two sites that are linked together – the clue is that the web addresses aren’t the same.

As you can from this blog, you can achieve a more integrated experience by using your own domain name for your moonfruit site (e.g., and configure a subdomain for your blog (e.g.

The instructions for this vary depending on who you buy your domain from.

Just be aware this involves a cost. You have buy a domain, create the subdomain, and you also have to pay for wordpress to map to your subdomain.

You will also need to point the C-record of your subdomain to wordpress.