David Fiske Affiliate Marketing & Web Development

We need 2 databases: the recipes and the advertisers. We could make everything flat file (i.e. without a database) and whilst that would be 'easier' now, it would be more problematic when the site grows.

Here's how I would set up the databases:
 
Table name: merchants

I would created 5 fields:

• id (INT, length 2, auto_increment, primary key)
• name (VARCHAR, length 100)
• banner (VARCHAR, length 255)
• url (VARCHAR, length 255)
• active (ENUM, value 'Yes','No')

ID is an automatically generated number assigned to each merchant. It is unique (hence labelled as a primary key) and is sequential (hence the auto_increment). I could have used the merchant name as a primary key but there are two reasons I didn't:

1. Firstly, I can create a handy "Go" script to redirect affiliate links.
2. Secondly, I may have the need for two identical named merchants but two separate banners/url's.

I'm not going to need more than 99 merchants so the length is 2. It's also an integer each time (round number not a decimal and not text) so we use INT.

The name is the merchant name, with a length of 100 as I don't suspect many merchants have a name longer than this for my example. VARCHAR is used as this allows a mixture of text and numbers to be used.

Banner is the url to the banner image. I'm going to use 468 x 60 throughout which is why I don't need to specify dimensions here. If you wanted to use various image sizes, you could do with adding 'width' and 'height' columns.

Url is the network tracking link. Both banner and url have a length of 255 as some networks have incredibly long tracking links.

Active is a simple column with 2 options - yes and no. If a merchant is set to inactive, the banner won't be shown in rotation. Why bother? Well if a merchant is suspended, you can set them as inactive. If you delete the banner code, it can be a pain re-adding it 24 hours later when they are unsuspended.

ENUM lets you create a finite number of options (in this case 'Yes' and 'No') to select from. I suppose would could have used VARCHAR but it's more common to use a restrictive tool such as ENUM.

I'm populating my table with merchants listed in the previous article, as well as some niche one's too.

Table name: recipes

This table is a bit more complex. I've decided to concentrate all the ingredients into one field. The alternative is splitting them up into separate fields - the benefit being that you could easily identify specific recipes by ingredients on a large scale. The reason I am taking the route I am is time related.

My table look like this:

• id (INT, length 4, auto_increment, primary key)
• name (VARCHAR, length 255)
• category (VARCHAR, length 255)
• summary (LONGTEXT)
• ingredients (LONGTEXT)
• recipe (LONGTEXT)
• date (DATE)

Again, ID is an integer and the primary key. As the recipe name could be the same for two or more recipes, we need a separate field that is unique.

Name is the recipe name and category is the category the recipe falls under. I've opted for VARCHAR for the category rather than ENUM as it allows me the freedom to create as many categories as I need to without having to edit the database structure. The risk here is that I spell a category wrong and it gets filed under a brand new category - this can be easily resolved however.

Summary, ingredients and recipe are all LONGTEXT which lets me store large amounts of text in the database. For anything larger than around 255 characters, I would recommend looking into LONGTEXT.

Summary is where I will briefly write about the recipe. Ingredients will comprise of a list of ingredients and recipe will explain how to put everything together. All of these fields will contain HTML formatting for simplicity. Summary will be formatted paragraphs, Ingredients will be an unordered list (ul) and recipe will be an ordered list (ol).

Date lets me specify today's date which is handy for creating an RSS feed based upon the latest recipes. It can also be used for the featured items on the home page. MySQL used the format YYYY-MM-DD where YYYY is the year, MM is the 2 digit month and DD is the 2 digit day. For example, 29th August 2008 is 2008-08-29.

This table is to be populated with between 5 and 10 recipes to get going.

You may be wondering about the recipe images. I've decided that I'm going to use uniform sized images named after the id. For example, recipe number 499 will have an image of standard size called 499.jpg. This is purely for simplicity although there is scope to add more fields for the image (such as imageurl, imageheight, imagewidth).

Extras

Where's the SQL code? Not here! I was going to post it but my SQL code outputted from my server is different to that on my previous web host's system. To avoid any kind of confusion, I've explained the steps in detail above rather than post the code.

Top tip: When working with MySQL, I tend to use lowercase letters as it is easier for me. Some people prefer mixed case. The important thing to remember is that whatever method you choose, try and stick to it.