23+ Impressive Tools for Faster Javascript/Ajax Development

Posted on June 26th, 2008 in JavaScript by Ashish  Tagged

Javascript applications are still one of the most complex issues when it comes to web-development. Many tools and applications could make your developing life pretty fast and simple. Today we wanted to share with you a wide range of tools and applications that could really help you build, test and debug Javascript and Ajax applications. Let us know your experience with the tools listed here or others that are not.
1-Roar - Notifications (v1.0) Roar is a notification widget that streamlines incoming messages, such as updates or errors, without distracting the user from their browser experience or breaking their work-flow by displaying obtrusive alerts.

javascript ajax tools


2-Damn It DamnIT is a free service that emails you when a user encounters a JavaScript error in your webpages.

javascript ajax tools


3-Growl 2.0 with Mootools Growl is a notification system for Mac OS X, it allows applications that support Growl to send you notifications.

javascript ajax tools


APIs

4-Google AJAX Language API With the AJAX Language API, you can translate and detect the language of blocks of text within a webpage using only Javascript. The language API is designed to be simple and easy to use to translate and detect languages on the fly when offline translations are not available.


5-Got API An easy to use interface that helps you reach the methods and functions of many JavaScript frameworks by getting the data from trusted sources and websites.

javascript ajax tools


6-WaveMaker Visual Ajax Studio WaveMaker’s Studio and Framework provides a powerful solution for rapidly developing web-based applications. Drag & drop assembly of widgets and service, code-free integration of web-services, databases and Ajax UI, and one-touch deployment to standard Tomcat servers all work seamlessly together to let you build complete web apps in hours!

javascript ajax tools


FrameWorks

7- SproutCoreSproutCore is a framework for building applications in JavaScript with remarkably little amounts of code. It can help you build full “thick” client applications in the web browser that can create and modify data, often completely independent of your web server, communicating with your server via Ajax only when they need to save or load data.

javascript ajax tools


8-JavaScriptMVCJavaScriptMVC is a framework that brings methods to the madness of JavaScript development. It guides you to successfully completed projects by promoting best practices, maintainability, and convention over configuration.

javascript ajax tools


Amazing Tools

9-Jaxer Jaxer is an Ajax server. HTML, JavaScript, and CSS are native to Jaxer, as are XMLHttpRequests, JSON, DOM scripting, etc. And as a server it offers access to databases, files, and networking, as well as logging, process management, scalability, security, integration APIs, and extensibility.

javascript ajax tools


10-The Regulator The Regulator is an advanced, free regular expressions testing and learning tool that allows you to build and verify a regular expression against any text input, file or web, and displays matching, splitting or replacement results within an easy to understand, hierarchical tree.

javascript ajax tools


11-SnippelySnippely is a basic text and code organizational tool. Instead of storing bits of code, quick notes, and memos in text files all over your hard drive, this application will let you save and organize “snippets” in one convenient location.

javascript ajax tools


12-NitobiBugIt’s a browser-based JavaScript object logger and inspection tool - similar to Firebug. NitobiBug runs across different browsers (IE6+, Safari, Opera, Firefox) to provide a consistent and powerful tool for developing rich Ajax applications. You can check a demo here.

javascript ajax tools


13- Google Mashup EditorThe Google Mashup Editor provides simple tools and features that allow you to create mashups in minutes with the following features:

  • A set of tags that compiles into AJAX UI components.
  • Syntax highlighting
  • Autocomplete of gm tags by pressing the tab button
  • Quick access to documentation for any tag by pressing F2
  • File upload and management
  • Error checking and notification

javascript ajax tools


14- Beautify JavascriptThis tool was intended to explore ugly javascripts, e.g compacted in one line, or just make scripts look more readable.


15- ThemeRollerThemeRoller allows you to design custom jQuery UI themes for tight integration in your projects.

javascript ajax tools


16- JSDocJSDoc is a tool that parses inline documentation in JavaScript source files, and produces an documentation of the JavaScript code.


17- Clean AJAX Clean is an open source engine for AJAX, that provides a high level interface to work with the AJAX technology. It was created to solve real problems found on AJAX applications, and is used in many projects. Clean will help you to adopt AJAX saving your time and your code, reducing your learning curve and the code reengineering.

javascript ajax tools


18- Sajax Sajax is an open source tool to make programming websites using the Ajax framework as easy as possible. Sajax makes it easy to call PHP, Perl or Python functions from your webpages via JavaScript without performing a browser refresh. The toolkit does 99% of the work for you so you have no excuse to not use it.

javascript ajax tools


19- JavaScript Code Improver With JavaScript Code Improver you are just one click away from making any JavaScript clear, easily comprehensible and ready for printing thus saving the time you spend on editing, debugging and modifying it.

javascript ajax tools


Browser Addons and Toolbars

20- Greasemonkey Greasemonkey is a Firefox extension that allows you to customize the way web pages look and function. You can use it to make a web site more readable or more usable. You can fix rendering bugs that the site owner can’t be bothered to fix themselves.


21- Web Developer extension for Firefox This tool is really excellent for quick and easy webdevelopment. It Adds a menu and a toolbar with various web developer tools.

javascript ajax tools


22-Internet Explorer Developer Toolbar The Microsoft Internet Explorer Developer Toolbar provides a variety of tools for quickly creating, understanding, and troubleshooting Web pages.


23-DebugBar An Internet Explorer plug-in that brings you new powerful features :

  • DOM Inspector: View DOM Tree and modify tags attributes and css attributes on the fly to test your page
  • HTTP Inspector: View HTTP/S request to check cookies, GET and POST parameters, view server info
  • Javascript Inspector and Javascript Console: View javascript functions for easier debugging, see Javascript and AJAX code
  • HTML Validator: Validate HTML code to correct and optimize your code and html size of your page
  • And many more features: See page cookies, get pixel color on a page, make a page screenshot.

javascript ajax tools


24-Firebug Firebug integrates with Firefox to put a wealth of web development tools at your fingertips while you browse. You can edit, debug, and monitor CSS, HTML, and JavaScript live in any web page. It can be downloaded from here.

javascript ajax tools

8 Great CSS Frameworks

Posted on June 26th, 2008 in Frameworks, HTML by Ashish  Tagged ,

Well, I guess I’m going to work backwords. There are three layers to the frontend; behaviour, presentation and markup. We’ve done behavior so we’re onto presentational now. CSS Frameworks have been all the buzz lately, we’ve had ones that use python ways of code then regenerate it as css, ones that are specifically for forms and an awesome one which is just for styling print!

Personally I find frameworks to much hassle, I have my own little framework but that is only really 20 or so lines. I might share it with all of you in the coming weeks if you would like it. Now here they are!

1. 960 Grid System - The 960 Grid System is an effort to streamline web development workflow by providing commonly used dimensions, based on a width of 960 pixels. There are two variants: 12 and 16 columns, which can be used separately or in tandem.

2. Blueprint CSS - Blueprint is a CSS framework, which aims to cut down on your CSS development time. It gives you a solid CSS foundation to build your project on top of, with an easy-to-use grid, sensible typography, and even a stylesheet for printing.

3. Hartija - The owner of Hartija describes it as a “universal Cascading Style Sheets for web printing”. It’s extremely customizable and very user friendly.

4. Formy CSS - Formy is mini CSS Framework for building web forms.

5. Clever CSS - CleverCSS is a small markup language for CSS inspired by Python that can be used to build a style sheet in a clean and structured way. In many ways it’s cleaner and more powerful than CSS2 is.

6. SenCSS - SenCSS is a sensible standards CSS Framework. This means that SenCSS tries to supply standard styling for all repetitive parts of your CSS, allowing you to focus on the fun parts. In that sense, senCSS is a bit like a large CSS Reset.

7. Elements -

Elements is a down to earth CSS framework. It was built to help designers write CSS faster and more efficient. Elements goes beyond being just a framework, it’s its own project workflow.It has everything you need to complete your project, which makes you and your clients happy. Read the Overview for more information.

8. LogicCSS - The Logic CSS framework is a collection of CSS files and PHP utilities to cut development times for web-standards compliant xHTML layouts.

This is just the tip of the iceberg, there are tones more css frameworks out there. I plan on releasing one for this site.

Friendly URLs in PHP: why do you care?

Posted on June 24th, 2008 in PHP by Ashish  Tagged

Nice URLs, readable URLs, search-engine-friendly URLs. Different names same deal.

It can’t really be disagreed on that something like example.com/index.php?page=article&id=409 is not nearly as nice as example.com/article/409.

Turns out this isn’t all that hard with PHP - infact it can turn into something that’s very useful from more than just a readability viewpoint.

The Plan

What we’re going to do is capture everything past a particular point in our URL and pipe it to PHP, we’re going to let PHP do most of the the legwork.

* Using mod_rewrite we get everything past a particular point.
* We will then pass that into a PHP get variable. We’ll use “p”, you can use anything.
* In our PHP we will define “rules” for our URLs with very simple regular expressions.
* When something matches, we’ll get the appropriate page - otherwise we’ll give back a 404.

mod_rewrite

Yep, this article is purely from an Apache perspective, so I assume that you not only have Apache on your webserver, that you also have enabled mod_rewrite.

If you haven’t enabled mod_rewirite, it’s usually something like this in your http.conf:

LoadModule rewrite_module modules/mod_rewrite.so

Remember to restart Apache afterwards.

.htaccess

Let’s get .htaccess out of the way now. These are special files that you may, or may not have come accross. Basically, Apache will read these set of definable rules and determines what to serve when you visit a page.

In this case we’re going to use mod_rewrite rules. So, first create a file called .htaccess and open it up. (To Windows folk - It really has no “filename”, this might confuse some of the poorer text editors.)

To start with, we’re going to check if mod_rewrite is enabled - if it isn’t then Apache will return an internal server error (500).

All our rules will go inside this block.

First we need to enable the rewrite engine and set our base.

RewriteEngine on
RewriteBase /

The RewriteBase will tell Apache exactly where our capturing needs to come from, what the relative path is.

In our case we’re assuming we’re rewriting the root of a URL. If your script resided in a sub-directory like http://example.com/test, RewriteBase would have to be written like so:

RewriteBase /test

Our first rule will tell Apache where to go when we haven’t specified anything, in this case we simply want to go to index.php:

RewriteEngine on
RewriteBase /

# Nothing - Go to index.php
RewriteRule ^$          index.php  [L]

RewriteRule is how we define our actual rules.

Rewrite rules use regular expressions (regex for short), a kind of special text processing language, to define the rules. They can become very useful if you learn them properly, and we shall be using them later when we write the PHP portion of this article. PHP comes with reasonably good regex support built in.

In this case our regular expression is ^$. The caret symbol, ^, means “At the start”. The dollar sign, $, means “At the end.” So this pattern effectively matches nothing, there is nothing between the two symbols.

Don’t worry if regular expressions are confusing at first, there are many tutorials on it around.

The second part of RewriteRule defines where our rule goes to, in this case we always want an empty path to go to index.php.

The last part in square brackets defines a special flag, here we use the L flag. L defines this rule as the “last one” - if this rule matches then Apache will disregard any further ones.

Our next rule is the one that we came here for:

RewriteEngine on
RewriteBase /

# Nothing - Go to index.php
RewriteRule ^$          index.php       [L]

# If anything matches, send the path to index.php
RewriteRule (.*)        index.php?p=$1  [L]

In regex a fullstop means “any single character” and asterisk means “one or more of the previous character”. So our pattern is effectively - any amount of any character or simply, everything.

The parentheses around the rule indicate we want to group that match, and use it later. We do use in defining where our pattern goes to. You refer to groups with the dollar sign and the number of group. (Groups start at 1.) Again we will define this as the last rule with [L].

So with these two rules http://example.com will invisibly redirect to index.php and http://example.com/page/409 will rewrite to index.php?p=page/409.

Unfortunatly, if you try to go directly to any other file or directory, it will not show up - Instead the path will get sent to index.php! We can use RewriteCond to add additional conditions to rectify this situation.

RewriteEngine on
RewriteBase /

# Nothing - Go to index.php
RewriteRule ^$          index.php        [L]

# Condition - requested path is not a file or directory.
RewriteCond %{REQUEST_FILENAME}         !-f
RewriteCond %{REQUEST_FILENAME}         !-d

# If anything matches, send the path to index.php
RewriteRule (.*)        index.php?p=$1  [L]

In a little bit of slightly backward behaviour, when Apache finds a rewrite rule it will go back to see if there were any conditions and check those before finally matching. So these conditions must appear before the rule.

The first parameter is what we test against, it refers to a special server variable, in this case REQUEST_FILENAME. The second parameter is our actual condition. -f asks “Is an existing file” and -d is directories.

Since we only want to continue if the match is not a file we can use a !, so our final patterns are !-f and !-d.

Our rewrite ruleset is nearly complete, but there’s one tiny thing, sometimes you may want to pass GET parameters around in the URL the same way you normally would, as it stands these will be ignored by Apache when rewriting. We can use a flag to append all extra request parameters to the URL - QSA.

Our final .htaccess file looks like this:

RewriteEngine on
RewriteBase /

# Nothing - Go to index.php
RewriteRule ^$          index.php       [L]

# Condition - requested path is not a file or directory.
RewriteCond %{REQUEST_FILENAME}         !-f
RewriteCond %{REQUEST_FILENAME}         !-d

# If anything matches, send the path to index.php
RewriteRule (.*)        index.php?p=$1  [QSA,L]

If anything about mod_rewrite is confusing it’s worth knowing that the documentation on it is actually not all that terrifying and can help you understand it much better.

Using PHP to get the requested URL

Our rewrite rule will pipe everything into index.php as a GET parameter called “p”. So everything in our script will need to be included through this.

To start our index.php file we need to define a list of matches.

/*
 * Work out what page we are on
 */
$page_rules = array(
  "index" => "home.php",
  "about" => "about.php"
);

$page_val = (isset($_GET['p'])) ? $_GET['p'] : “index”;

$page_rules is an associative array pointing matched rules to special pages. You can keep your eventually pages whereever you like, for our example we’ll simply have a “pages/” subdirectory where these will go in.

We set $page_val to our “p” parameter if it was set, otherwise defaulting to index.

Next we go through all our rules and find if we’ve matched one with our parameter.

/*
 * Work out what page we are on
 */
$page_rules = array(
  "index" => "home.php",
  "about" => "about.php"
);

$page_val = (isset($_GET['p'])) ? $_GET['p'] : “index”;

/*
 * Iterate through rules and test them on our request
 */
$match = NULL;

foreach($pages as $regex => $page_to)
{

  if(preg_match(”/^”.$regex.”$/i”, $page_val))
  {
    $match = $page_to;
    break;
  }

}

/*
 * If we didn’t match anything deliver a 404 or include our file
 */
if($match === NULL)
  die(”404″);
else
  require “pages/”.$page_to;

We now use preg_match to match against each of our rules.

For our index rule, the regular expression looks like /^index$/i. The forward slashes indicate the start and end delimeters of the regex, we can use extra flags to define special things, in this case we use the i flag which indicates that the match is case insensitive.

When we match a rule, we save the page name, then serve it later, or give a 404 message if not. (Your 404 message should be a lot better - this is a simple example after all.)

At this point our rewriting will work perfectly fine. There’s a few issues to work out first.
Small issues

First off, if you go to http://example.com/about the rule works, but if you go to http://example.com/about/ then nothing matches - our p variable has changed with that one character!
Luckily we can expand our regular expression to account for this. Question mark, ?, means “the previous character none or more times”. So /? would mean “forward slash or no forward slash”, except that as we know, forward slash means something in regular expressions. To escape characters in regex simply use the backslash character. The correct match is \/?.
Our new rule check looks like this:

if(preg_match("/^".$regex."\/?$/i", $page_val))
{
$match = $page_to;
break;
}

You may be starting to think that regular expressions can start to look messy the more complicated they get - you’d be right, but that doesn’t discount how useful they can be. A regular expression can be broken down into smaller parts if necessary.
This leads on to our second problem, if any of our matches have a forward slash in, which they most likely will, you will get an error when hitting the regex because it would be unescaped. A quick str_replace will sort that.

$regex = str_replace("/", "\/", $regex);

if(preg_match("/^".$regex."\/?$/i", $page_val))
{
$match = $page_to;
break;
}

Grouping
This is all well and good, but if you have a URL like our initial example of http://example.com/article/409 surely you’d have to make a new rule for each ID? Luckily we can leverage the power of regex to do that for us.
First we need to ammend our rules to match /article/ and any number. This is done like so:

$page_rules = array(
  "index"          => "home.php",
  "about"          => "about.php"
  "article/[0-9]+” => “article.php”
);

[0-9] is a range, a single number from 0 to 9. And whereas ? means “zero or more times”, in this case we need to use plus, + which means “one or more times“. So we’re now matching any integer.
Great, our rule works! But it’s useless to us if we can’t get that parameter out. Well, first we need to define this as a group in our regex by using parentheses, the same way we did in our .htaccess rules before.

$page_rules = array(
  "index"            => "home.php",
  "about"            => "about.php"
  "article/([0-9]+)” => “article.php”
);

To get our groups out of our regular expression we need to use the third parameter of preg_match, in which we pass in an array that will then be filled with our groups.
Our foreach loop needs to look something like this:

$match = NULL;
$matches = array();

foreach($pages as $regex => $page_to)
{

$regex = str_replace("/", "\/", $regex);

if(preg_match("/^".$regex."\/?$/i", $page_val, $matches))
{
$match = $page_to;
break;
}

}

Great, now if you tried to go to /article/409 and did a print_r of $matches, you’d get something like:
Array

(

[0] => article/43/

[1] => 43

)
The first entry is filled with the entire pattern, the second one is our requested ID - the group we defined in our match.
Notice that because we’ve defined any number, if you try to enter anything other than a number into the URL as an ID you will get given a 404 - This can act as a kind of first-defense validation for input variables.
This is almost the end of the article, however we can make this a little nicer to work with.
Named groups
Being able to refer to something like $_GET[’id’] is a convenience, there’s a name. $matches[1] is not so easy to dechyper - or even keep track of, what if we add another group that appears before it like a category name - the group number may shift.
Luckily with regex we can name our groups using the simple syntax of ? inside the start of the group. Our new rules can now look like this.

$page_rules = array(
  "index"                 => "home.php",
  "about"                 => "about.php"
  "article/(?[0-9]+)” => “article.php”
);

Now when you print_r $matches you get:
Array

(

[0] => article/43/

[id] => 43

[1] => 43

)
You can still refer to the group as $matches[1], but we’re also given the option of $matches[’id’].
Conclusion

Our final PHP script looks like this:

/*
* Work out what page we are on
*/
$page_rules = array(
"index"                 => "home.php",
"about"                 => "about.php"
"article/(?[0-9]+)” => “article.php”
);

$page_val = (isset($_GET['p'])) ? $_GET['p'] : “index”;

/*
* Iterate through rules and test them on our request
*/
$match = NULL;
$matches = array();

foreach($pages as $regex => $page_to)
{

$regex = str_replace(”/”, “\/”, $regex);

if(preg_match(”/^”.$regex.”\/?$/i”, $page_val, $matches))
{
$match = $page_to;
break;
}

}

/*
* If we didn’t match anything deliver a 404 or include our file
*/
if($match === NULL)
die(”404″);
else
require “pages/”.$page_to;

?>

We’ve implemented friendly URLs in a simple way that is also very easy to work with in your script, we even have some basic validation that wouldn’t get relying on $_GET.

15 Tools to Help You Develop Faster Web Pages

Posted on June 24th, 2008 in Techniques by Ashish  Tagged

Response times, availability, and stability are vital factors to bear in mind when creating and maintaining a web application. If you’re concerned about your web pages’ speed or want to make sure you’re in tip-top shape before starting or launching a project, here’s a few useful, free tools to help you create and sustain high-performance web applications.

I’ve tried to include a wide variety of tools that are easy to use, and have tried to keep them as OS and technology-independent as possible so that everyone can find a tool or two.

1. YSlow for Firebug

YSlow for Firebug - Screenshot

YSlow grades a website’s performance based on the best practices for high performance web sites on the Yahoo! Developer Network. Each rule is given a letter grade (A through F) stating how you rank on certain aspects of front-end performance. It’s a simple tool for finding things you can work on such as reducing the number of HTTP request a web page makes, and compressing external JavaScript and CSS files. A worthwhile read is the Ajax performance analysis post on IBM developerWorks that outlines practical ways of using YSlow in your web applications.

2. Firebug

Firebug - Screen shot

Firebug is an essential browser-based web development tool for debugging, testing, and analyzing web pages. It has a powerful set of utilities to help you understand and dissect what’s going on. One of the many notable features is the Net (network”) tab where you can inspect HTML, CSS, XHR, JS components.

3. Fiddler 2

Fiddler 2 - Screen shot

Fiddler 2 is a browser-based HTTP debugging tool that helps you analyze incoming and outgoing traffic. It’s highly customizable and has countless of reporting and debugging features. Be sure to read the “Fiddler PowerToy - Part 2: HTTP Performance” guide on the MSDN which discusses functional uses of Fiddler including how to improve “first-visit” performance (i.e. unprimed cache), analyzing HTTP response headers, creating custom flags for potential performance problems and more.

4. Cuzillion

Cuzillion - Screen shot

Cuzillion is a cool tool to help you see how page components interact with each other. The goal here is to help you quickly rapidly check, test, and modify web pages before you finalize the structure. It can give you clues on potential trouble-spots or points of improvements. Cuzillion was created by Steve Saunders, the ex-Chief Performance at Yahoo!, a leading engineer for the development of Yahoo’s performance best practices, and creator of YSlow.

5. mon.itor.us

mon.itor.us - Screen shot

monitor.us is a free web-based service that grants you a suite of tools for monitoring performance, availability, and traffic statistics. You can establish your website’s response time and set up alerts for when a service becomes unavailable. You can also set-up weekly, automated benchmarks to see if changes you’ve made impact speed and performance either positively or negatively.

6. IBM Page Detailer

IBM Page Detailer - Screen shot

The IBM Page Detailer is a straightforward tool for letting you visualize web components as they’re being downloaded. It latches onto your browser, so all you have to do is navigate to the desired site with the IBM Page Detailer open. Clicking on a web page component opens a window with the relevant details associated with it. Whenever an event occurs (such as a script being executed), the tool opens a window with information about the processes.

7. Httperf

Httperf is an open-source tool for measuring HTTP server performance running on Linux. It’s an effective tool for benchmarking and creating workload simulations to see if you can handle high-level traffic and still maintain stability. You can also use it to figure out the maximum capacity of your server, gradually increasing the number of requests you make to test its threshold.

8. Pylot

Pylot - Screen shot

Pylot is an open-source performance and scalability testing tool. It uses HTTP load tests so that you can plan, benchmark, analyze and tweak performance. Pylot requires that you have Python installed on the server - but you don’t need to know the language, you use XML to create your testing scenarios.

9. PushToTest TestMaker

PushToTest TestMaker - Screen shot

PushToTest TestMaker is a free, open-source platform for testing scalability and performance of applications. It has an intuitive graphical user interface with visual reporting and analytical tools. It has a Resource Monitor feature to help you see CPU, memory, and network utilization during testing. The reporting features let you generate graphs or export data into a spreadsheet application for record-keeping or further statistics analysis.

10. Wbox HTTP testing tool

Wbox HTTP testing tool - Screen shot

Wbox is a simple, free HTTP testing software released under the GPL (v2). It supports Linux, Windows, and MacOS X systems. It works by making sequential requests at desired intervals for stress-testing. It has an HTTP compression command so that you can analyze data about your server’s file compression. If you’ve just set up a virtual domain, Wbox HTTP testing tool also comes with a command for you to test if everything’s in order before deployment.

11. WebLOAD

WebLOAD - Screen shot

WebLOAD is an open-source, professional grade stress/load testing suite for web applications. WebLOAD allows testers to perform scripts for load testing using JavaScript. It can gather live data for monitoring, recording, and analysis purposes, using client-side data to analyze performance. It’s not just a performance tool – it comes with authoring and debugging features built in.

12. DBMonster

DBMonster - Code Screen shot

DBMonster is an open-source application to help you tune database structures and table indexes, as well as conduct tests to determine performance under high database load. It’ll help you see how well your database/s will scale by using automated generation of test data. It supports many databases such as MySQL, PostgreSQL, Oracle, MSSQL and (probably) any database that supports the JDBC driver.

13. OctaGate SiteTimer

OctaGate SiteTimer - Screen shot

The OctaGate SiteTimer is a simple utility for determining the time it takes to download everything on a web page. It gives you a visualization of the duration of each state during the download process (initial request, connection, start of download, and end of download).

14. Web Page Analyzer

Web Page Analyzer - Screen shot

The Web Page Analyzer is an extremely simple, web-based test to help you gain information on web page performance. It gives you data about the total number of HTTP requests, total page weight, your objects’ sizes, and more. It tries to estimate the download time of your web page on different internet connections and it also enumerates each page object for you. At the end, it provides you with an analysis and recommendation of the web page tested – use your own judgment in interpreting the information.

15. Site-Perf.com

Site-Perf.com - Screen shot

Site-Perf.com is a free web-based service that gives you information about your site’s loading speed. With Site-Perf.com’s tool, you get real-time capturing of data. It can help you spot bottlenecks, find page errors, gather server data, and more - all without having to install an application or register for an account.

More Tools and Related Resources

If you have a favorite web performance tool that wasn’t on the list, share it in the comments. Would also like to hear your experiences, tips, suggestions, and resources you use.

Courtesy: sixrevisions

Google Android vs. iPhone: Same as Microsoft vs. Macintosh ?

Posted on June 24th, 2008 in Google Android, iPhone by Ashish  Tagged ,

android_vs_iphone_thumb4.jpegBack in 1984, Apple was on top of the computing world with top-notch sexy hardware and it partnered with Microsoft for some top-notch [not so] sexy practical software. It was a winning combination for both Apple and Microsoft. Microsoft’s strategy in 1984 was hardware agnostic making its software available on any popular platform. Apple’s strategy in 1984 was holistic. We’ll call the period between 1984 and 2000, Round 1.

In Round 1, it turned out that Microsoft’s strategy was the clear-cut winner. By being hardware agnostic, hardware vendors competed with one-another to drive down the price of hardware much faster than anyone could have imagined. Clone prices fell so fast and so much lower than the price of a Macintosh that it simply became impractical to own a Mac. Software vendors also took note and quickly the non-Mac-PC became the standard. Apple nearly died.

2007 set off Round 2. This time around it’s in the cell phone business. Once again, Apple is on top of the Cell Phone game with top-notch sexy hardware and it has partnered with Google this time for some top-notch [not so] sexy practical software (think Google Maps, YouTube and other web-based Google Apps for the iPhone). Once again, it’s a winning partnership for both Apple and Google. And Once again, Google’s strategy is exactly the same to that of Microsoft in 1984: stay hardware agnostic. Apple’s strategy is also identical to its own strategy back in 1984: stay holistic. Round 2 has begun.

The similarities are eery. In 1984, while Microsoft was building the most popular application software on the Mac, it had begun a similar hardware-agnostic operating system (Windows) on its own. In 2007, while Google has some of the most popular application software on iPhone, it has begun a similar hardware agnostic Cell-Phone operating system (Android) on its own. Apple’s strategy has not changed a single bit. It refuses to license its operating system or any other technology while it continues to want more and more control over the entirety of the experience. The iPhone in 2007 has set off a brand new race much like the original Macintosh did in 1984. Interestingly, 24 years later, the strategies are still identical on both sides.

So will Round 2 end in the same way with Google’s Android prevailing due to exceptionally cheap phones that are sure to emerge using Android? Maybe not. There is one key difference between Round 1 & Round 2: Steve Jobs. Steve didn’t get to finish fighting the strategy that he helped establish for Apple in Round 1. He left Apple in 1985. So there’s no telling how things would have turned out. The fact that Apple lost round 1 may have taught everyone a lesson and it might falsely embolden Google to think Microsoft’s winning strategy was the better strategy. After all, Google’s chief, Eric Schmit, has been learning from (and losing to) Microsoft for 20+ years. Eric is now using Microsoft’s own strategy to successfully beat them. Google is the new Microsoft.

But this time around Steve is much smarter than he was in 1984. So smart in fact, that he’s resurrected Round 1 from the dead and may still pull off a win (the Mac is coming back). It’s possible that Steve & Apple’s holistic approach will still be the winning approach for Round 1, assuming you extend round 1 to at least 2015. But in Round 2 Apple’s chances are a lot better. Everybody is at the beginning of the race. There are no clear winners and just like in 1984, Apple has a major lead. It’ll be interesting to see what happens.

I’m curious to know your thoughts on:

  • Who you think will win Round 2: will it be Google with its Android or Apple with its iPhone?
  • Is Round 1 over in your view or will the Mac eventually beat out Windows-based PCs in market share?
  • Is Microsoft even a player in Round 2? (I would never recommend a current Windows-Mobile phone to my worst enemy - so do they even stand a chance?)

Setting-Up a Relational Database in MySQL

Posted on June 18th, 2008 in MySQL, PHP by Ashish  Tagged

Relational Database Design is one of the most powerful ways to ensure data integrity and a great way to kick-off any project. Very often the first thing developers do when starting a new project, or stub-project, is to design the database. This way the structure of the application is already in place and we just have to fill in the pieces with some server-side code. I’ve found when adding relational constraints to your database design you add in a very powerful error reporting tool that will let you know during the development process that you have allowed something to happen that shouldn’t have. In this article, I go through, step by step, showing how to set up a simple relational database and discuss the benefits that are enjoyed.

Let’s take a step back and describe what a relational database looks like. In any normal database design there are fields in one table that reference another table. For example, a books table might have a field labeled author_id which is meant to come from a table named authors. Creating hard-coded relations solidifies these associations and actually returns a MySQL error if violated.

As I hinted in the opening I have found this to be invaluable during the development and testing process as MySQL will immediately let me know that I have made a glaring error that otherwise may not have been noticed until after the service has launched. At that point the data could be irreparably corrupt and forced to start from scratch.

So let’s get right to it. For the purposes of this article, I’m going to pretend I’m creating a simple Books and Authors website with a simple 2-table setup. The first step is to create our tables.

  1. CREATE TABLE `library`.`books` (
  2. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  3. `name` VARCHAR( 150 ) NOT NULL ,
  4. `author_id` INT UNSIGNED NOT NULL ,
  5. PRIMARY KEY ( `id` ) ,
  6. INDEX ( `author_id` )
  7. ) ENGINE = InnoDB

  1. CREATE TABLE `authors` (
  2. `id` int(10) unsigned NOT NULL auto_increment,
  3. `name` varchar(50) collate utf8_bin NOT NULL,
  4. PRIMARY KEY  (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Relational Database - 1

Nothing too fancy here. Couple things to notice:

  1. Each table MUST be using the InnoDB storage engine. InnoDB is currently the only main-stream storage engine offered by MySQL to support relational design. More on this in my article: MyISAM vs InnoDB
  2. The `author_id` field in the `books` table MUST be indexed and the same datatype as the `id` field in `authors`.

The next step is to set up the relations. Open the `authors` table and take a look at the view. Under the table there should be a link titled ‘Relation View’ - Click it.

Relational Database - 2

phpMyAdmin has a great gui for setting up relations and actions. If the `author_id` row below doesn’t look like mine, make sure you have it indexed.

Relational Database - 3

Here, I’ve setup a link on the `books` table and the `author_id` field. This will enforce the fact that any value inserted in this field MUST be present in the `authors.id` table as well. But what about these other settings?

ON DELETE:

  • CASCADE:
    • This means if an author is deleted from the authors table, all of his books will also be automatically deleted.
    • This option is great to keep your data clean and reduce the number of delete quieries required when deleting an author.
  • SET NULL:
    • Instead of deleting the book record when an author is deleted, books.author_id is set to NULL, effectively orphaning the book.
    • This feature is great if you want to be able to keep the books and come back at a later time to reassign them. Otherwise, without this feature, the books would still be referencing an author_id that doesn’t exist.
    • Note: If you try to set this option and phpMyAdmin tells you to check your datatypes, make sure the field is allowing null values.
  • NO ACTION:
    • When a delete query is issued on an author that has books, MySQL will not allow this and return a Foreign_Key Constraint error.
    • It could be nice to identify this and re-word it to let the user know that if they would like to delete this author they need to re-assign his books or delete them all-together.
    • Note: If you use this option please remember to re-word the MySQL error to something the user can easily understand.
  • RESTRICT:
    • Same as NO ACTION
    • From MySQL Manual: Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same.

ON UPDATE:

  • For the most part the options described above are going to act in the same manner they did for ON DELETE as they will with ON UPDATE. I’ll just run through some examples real quick.
  • CASCADE:
    • If, for some reason, an author’s id gets updated than CASCADE will update all his corresponding books with the new value. Extremely handy.
  • SET NULL
    • Same as CASCADE except instead of updating it with the new value, it will set it to null. I’m sure there is a perfectly good use for this but I haven’t run into it yet. If anyone can enlighten me please do :)
  • NO ACTION / RESTRICT:
    • Same as ON DELETE, will throw an error if you try to update an author_id. I’m also having trouble finding a real-world example of when this could be useful

Once we have our simple relational database configured try to add a book with an author_id that doesn’t exist. MySQL should give you an error like this:

Cannot add or update a child row: a foreign key constraint fails (`library/books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

A developer’s perspective on Google’s Android

Posted on June 18th, 2008 in Google Android, PHP, iPhone by Ashish  Tagged ,

On Monday, November 12, 2007, Google released Android, a complete Linux based software stack aimed directly at the cell phone marketplace. I’ll let others talk about what it means for other players in the marketplace, the intricacies of GPL2 vs the Apache License, etc. This article dives straight into the heart of the SDK and API itself, summarizing some of the documentation provided by Google, then jumping into building an application using Android.


Android Emulator

(Click to enlarge)

So, what Is Android?

Android is a complete software stack for mobile devices such as cell phones, PDAs and high end MP3 players. The software stack is split into four layers:

  • The application layer
  • The application framework
  • The libraries and runtime
  • The kernel

Cell phone users obviously work with applications in the application layer. Android developers write those applications using the application framework. Unlike many embedded operating environments, Android applications are all equal — that is, the applications that come with the phone are no different than those that any developer writes. In fact, using the IntentFilter API, any application can handle any event that the user or system can generate. This sounds a bit scary at first, but Android has a well thought-out security model based on Unix file system permissions that assure applications have only those abilities that cell phone owner gave them at install time. The framework is supported by numerous open source libraries such as openssl, sqlite and libc. It is also supported by the Android core libraries — more on that in a second. At the bottom of the stack sits the Linux 2.6 kernel, providing the low level hardware interfaces that we all expect from a kernel. This is a Unix based system — that is, the Unix C APIs are available — but don’t expect to drop to a shell and start executing shell scripts with your old friends grep and awk. Most of the Unix utilities are simply not there. Instead Android supplies a well thought out API for writing applications — in Java using the Android core libraries.

That’s right, Android applications are almost exclusively written in Java. The Android core library is a big .jar file that is supported by the Dalvik Virtual Machine — a fast and efficient JVM work-alike that enables java-coded applications to work on the Android cell phone. This is similar to, but not the same as using Sun’s JVM directly.

Building your development environment

Google provides three versions of the SDK; one for Windows, one for Mac OSX (intel) and one for Linux (x86). They also provide two development environments — one is Eclipse based, and the other is a “roll your own.” The Eclipse based environment is quite feature-rich and should suffice for most developers. There is no reason that you can’t use both Eclipse and “roll your own.”

If you get stuck in eclipse (like I did), you may find yourself dropping to the command-line interfaces to see what’s really going on. However for this article, I’ll assume that you’re using the Eclipse IDE for your Android software development needs. Once you’ve downloaded the Android SDK, Eclipse and the Eclipse Plugin, you should work through the first few sections of Google’s install document (System and Software Requirements, Installing the SDK, Installing the Eclipse Plugin). I’d leave the rest of the document for later as it does get quite detailed quickly.

Read the Friendly Manual

Google has done a good job of writing a lot of documentation for Android. However, there isn’t a great way of knowing what’s important to read now vs. what can wait. Here are some links to documents that are important to understand what Android is and how to develop applications using it. If you read them in the order listed, then you’ll gain understanding more quickly as you read increasingly detailed documents. Note that a lot of the documentation is available both online and in the SDK_ROOT/docs/index.html directory on your machine. If you have a fast enough connection, I would suggest using the on-line versions since they will be more up to date.

Here’s the order in which I suggest you read the documentation:

  • What is Android? Explains what Android is and gives a high-level overview of its features and architecture. Don’t dive into the links just yet — just get a feel for this overall document.
  • Read the Anatomy of an Android Application page. This details the four building blocks of an Android app: Activity, Intent Receiver, Service and Content Provider. Again, don’t follow the links just yet — just get an overview of the architecture. You may want to reread sections on Activities and Intent Receivers — gaining an understanding of these concepts is critical to understanding how to build an Android application. If you don’t get it yet, you’ll see it again when you go through the Notepad Application Tutorial.
  • Next read the Development Tools document. Again — just get a flavor, don’t dive into the detail yet.
  • Read the Lifecycle of an Android Application page.
  • Now, finally, it’s time to get your hands dirty. Work through the Hello Android page. Make sure you actually do it using Eclipse.
Note: I had a problem here. The first time I ran the application, it worked fine. However on subsequent runs my application would not appear on the emulator. I killed and restarted the emulator, killed and restarted eclipse several times — no joy. Finally, somewhat frustrated, I was going to re-install everything. However before I did, I found an invisible instance of the Android Debug Bridge (adb) running. I killed it and everything worked again. I thought I had to close the emulator after each run of an application, but this turns out not to be the case. When I closed the emulator after my first run, it left the instance of adb running, which interfered with the subsequent instances.
  • Now go back and read the rest of the Installing the SDK document — the bottom half of it details some great debugging tips and features.
  • Next, go through the Notepad Application Tutorial. This is where the rubber really meets the road. If you spend the time to go through this series of exercises and really understand the code, you will be well on your way to becoming an Android expert.
  • Read the Developing Android Applications pages. This will take some time — these articles go into a lot of detail about several topics including how to implement the UI, data storage/retrieval and the security model.
  • Finally, go back through this list and follow the links in the previous documents as topics interest you.

There’s a lot of documentation, but if flows together nicely, reflecting the architecture of the environment.

Dissecting the SDK

Whenever I download an SDK, I like to take a look at the files I’ve installed. Often, there is a wealth of information hidden in the SDK itself that is not readily visible from the documentation. So here’s what you’ll find in the Android SDK on a Windows machine:

  • android.jar - The Android application framework. Unzipping this jar reveals the entire class structure and all of the supporting classes of the framework. Currently there is no source.
  • docs - 100 megabytes worth of documentation, samples, etc.
  • samples - Six different sample applications - ApiDemos, HelloActivity, LunarLander, NotePad, SkeletonApp and Snake
  • tools - the various SDK binaries such as aapt, acp, and emulator live here.
    • lib - various templates and supporting jar files live in this directory
      • activityCreator - the activityCreator python application lives here.
      • images - The Linux file system images are found in this directory: ramdis.img, system.img and userdata.img. They are YAFFS2 file system images, so I couldn’t open them without additional kernel support on my Fedora system.
        • skins - supporting emulator graphics for HVGA and QVGA screens in both landscape and portrait format.

Exercising the SDK

Now that you have read the documentation and set up and debugged a simple project, it’s time to look at some real code. Since Google has provided us with several sample applications, the best place to begin is by examining them.

  1. If you have not already done so, execute the first few sections of Google’s install document. Stop after you’ve installed the Eclipse plugin successfully.
  2. Now work through the Hello Android page if you haven’t already. This will get you started working with Android applications and the debugger. Note that it’s probably a good idea to create a new workspace for your Android projects if you already use Eclipse.
  3. Next we’re going to set up Eclipse projects for each of the sample applications. You can never have too much sample code. I’ll walk through setting up the Lunar Lander example and leave it as an exercise to the reader to set up the rest.
    1. Bring up the same Eclipse workspace that you used for the Hello Android, and close the project (Right click on the project in package explorer->Close Project).
    2. File->New->Android Project
    3. Project Name: LunarLander
    4. Click the “Create Project from existing source” radio button
    5. Browse to the samples/LunarLander directory in the SDK. If you find the right directory, the Properties fields will auto-fill with the correct information from the Package.
    6. Click Finish
    7. Bring up Eclipse’s Console window (Window->Show View->Console) if it’s not already visible in a tab at the bottom of the screen. It will show you the build process that Eclipse went through to create the application.
    8. Create a Run Configuration: Run->Open Run Dialog
    9. Highlight “Android Application” in the treeview to the left.
    10. Click the “New button”.
    11. Name: Lunar Lander
    12. Click the Browse button next to Project
    13. Double-click the LunarLander project and hit OK
    14. Click the down arrow for the Activity and choose the one and only Activity: com.google.android.lunarlander.LunarLander
    15. Click Apply
    16. Click Run
    17. Switch to the Emulator and play a few rounds of Lunar Lander. Kinda fun.

Repeat for the other applications in the samples directory. This exercise should only take a few minutes — besides, the Snake game is fun too! If you’ve taken the time to go through the Notepad Application Tutorial, then you’ll be familiar with the NotePad sample — however, the NotePad sample is fully developed and has features beyond the NotePad developed during the Tutorial.

A File System Explorer Application

Finally, we’ll use our new understanding of the Android to develop a simple file system explorer. The version in this article is pretty simple, but it can serve as a jumping-off point for a more serious application down the road.

Design

Before we start writing code, let’s think about what a reasonable file system browser should do. It should

  • Phase I features
    • Show a list of files and directories
    • Allow the user to navigate through the directory structure by clicking on directories
    • Warn the user that he has clicked on a file
  • Phase II features
    • Allow the user to display a dump of a file when it is clicked
    • Use a tree view instead of a simple list
    • Show a dialog box with the filesystem information (size, permissions, etc) when the user clicks on an icon next to each file
    • Give this application permissions to read any file on the file system
  • Phase III features
    • Do all of phase II with pretty graphics, such as thumbnails, instead of boring dropdowns and list boxes
    • Execute applications that we understand, such as mp3 files

Process

This article will only cover Phase I of the project — but when we’re done, we’ll have a functional file system explorer in just a few dozen lines of code.

To proceed with this hands-on example, click here.

New Android Project
(Click to enlarge)
It works!

If you clicked above to follow the hands-on example, you found that in about twenty lines of Java, and a small amount of XML, you’ve created a useful little application that will allow you to explore the Android’s file system. For example, I found the ringtones in /system/media/audio/ringtones, as shown below.


Oooh, ringtones
(Click to enlarge)

As I mentioned in the design section, a lot can be done with this application, and we’ve hardly touched the surface of what you can do with the Android application environment. There’s three billion cell phones out there. I suspect Google will get their fair share of them, so start cranking out code!

Conclusion

Android is a well-engineered development environment. Writing an Eclipse plug-in was a smart move by Google — one that should be emulated by other SDK developers. Eclipse gives a developer and environment where he can really think about the business problem without worrying about the boring details. Adding the functionality of the plugin helps developers just sit down and start coding — without having to worry about all the ins and outs of configuration files and the like.

Dislikes

Android is brand new to the general developer’s world. As I write this, it’s Wednesday, and the SDK came out on Monday of this week. Since it’s brand new, there are some little problems that will have to be solved in the coming releases.

  • Many more examples for the APIs.
  • A more thorough explanation of what does and does not work under the emulator. My first example application was a simple MP3 player.
  • Release the source code. This will make it a lot easier to debug Android applications, as well as write them in the style that the Google developers wrote them.

Likes

Theres a lot to like about Android:

  • It’s by Google — so it has a company with some clout behind it.
  • Application Developers write their code in Java. Since the learning curve for Java is much less than that of C/C++/ObjectiveC, there will be many many developers who are eager to start writing applications for Android.
  • The SDK and API are well designed. There is some complexity there, and as I mentioned, the Documentation needs improvement — but a well designed system is easier to understand and learn, even without lots of great examples.

PHP: Securing Your Input Forms From MySQL Injection Attacks

Posted on June 12th, 2008 in MySQL, PHP by Ashish  Tagged ,

Every website has ‘em. Forms. Places for users to enter data into your website. Whether it be a search box, a “Contact Us” form, or variables in the website address, at some point in the flow of your script these suckers are going to touch your database. Oh, that’s no problem — We’ll just take what they type in and run a query in MySQL on it!

WHOA, there! Are you sure you want to do that? Any input from a user should be treated like a nuclear fuel rod. You can handle it, but you’ve got to make sure you do it right. You wouldn’t just pick it up with your bare hands, would you?

Why? Just what are MySQL Injection attacks anyway?

Lets say your database has a table inside called ‘tbl_Users’. Inside ‘tbl_Users’ are a list of your users, which all have usernames, passwords, first names, last names, addresses, etc. If these users are presented with a login box somewhere on your site, your php user verification query might be something like this:

SELECT * FROM `tbl_Users` WHERE `username`=’”.$_POST['username'].”‘ AND `password`=’”.md5($_POST['password']).”‘”The problem is that unscrupulous users (read: bad ones) could enter this into your form:

 

username: no_onepassword: ‘ OR ”=”Which would make your query look something like this:

SELECT * FROM `tbl_Users` WHERE `username`=’no_one’ AND `password`=” OR ”=”Which, if you read that correctly, would allow that user access to whatever it was you wanted hidden by logging them in. There are a multitude of other ways this can be dangerous, but this is by far the easiest example. Even more unscrupulous users (read: the real jackasses) could send in multiple queries including DELETE queries.

In which case, when you wake up the morning after the attack you are most likely to be heard saying:”Hey, where did all my users go?” 

Wow. Okay so I’ve got a friend… and his website isn’t secure. What can I do to help him out?

The good news is that with a few easy precautions, your “friend’s” website will be pretty secure against these types of attacks. I say pretty secure because there is no way to prevent every attack. We can only do our best to increase security to a point to take every realistic precaution to prevent these attacks.

#1: Escape your variables!

Using the php function ‘mysql_real_escape_string’ you can “escape” the single quote character from user input. This is probably the easiest method to prevent MySQL injection attacks. It works by adding a backslash (”\”) before each quote that the user enters into their input. So, to use our example from before:

username: hey’therebecomes

username: hey\’thereThis effectively stops MySQL injection in its tracks since it not only escapes the single quote (”‘”) character but also all other characters that the baddies can use to hijack your queries.

If you’ve got an array of data coming in, you can use this neat function that I found on the PHP mysql_real_escape_string page (code by “brian dot folts at gmail dot com”). It escapes all of the values in your array with ease.

To escape an array, use this function:

function mysql_real_escape_array($t){
return array_map(”mysql_real_escape_string”,$t);
}

Then you can call that function easily by passing your array to it:

$your_array = mysql_real_escape_array($your_array);

#2: Check the variable type of your input.

This is done by using the php functions “is_numeric()“, “is_string()“, “is_float()“, and “is_int()” to determine if the input the user is sending in is the same type that you were asking for. It’s not perfect, but if you were asking for a number and they sent in a word you know to discard it straight away and return an error thereby entirely avoiding any change of a MySQL injection attack.

#3: Always use proper MySQL syntax, including “`” and “‘” characters.

If your queries look something like this:

SELECT * FROM tbl_Users WHERE username=$value; Rewrite it so that it looks more like this:

$value = mysql_real_escape_string($value);mysql_query(SELECT * FROM `tbl_Users` WHERE `username`=’”.$value.”‘”); Proper MySQL syntax requires that all table and field names are surrounded by the reverse apostraphe (”`”) and values surrounded with single quotes / apostraphe (”‘”).

I hope this gives you a better indication of what you can do to help secure your websites. Keep in mind that this is in no way a complete list. Be ever vigilant in your efforts to prevent attacks of any kind on your code. Leave a comment or two if this helped you at all or if you have different suggestions on how to secure your code from injection attacks!

Database Normalization and Table structures

Posted on June 3rd, 2008 in Design Principles, MySQL by Ashish  Tagged

Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form) and BCNF (Boyce-Codd Normal Form). There are others but they are rarely if ever used. A database is said to be Normalised if it is in 3NF (or ideally in BCNF). These steps are descibed as follows:

Note: When attribute is used we are speaking of a field in the table

1NF
To put a database in 1N

  • ensure that all attributes (columns) are atomic (which means that any single field should only have a value for ONE thing).

Examples:

In a database a table on Customers would have an address attribute. The address is made up of Company Name, Address Line1, Address Line2, Address Line3, City, Postcode. There are 6 values to this address and as such each should have it’s own field (column).

If your company sold furniture a table on products could have a description attribute. If for example that attribute was ‘Beech Desk 120w x 75h x 50d’. Ideally this would be broken down into a number attributes like ‘Colour’, ‘Type’, ‘Width’, ‘Height’ and ‘Depth’. The reason for this is it would allow you to seach the database for all Desks, for all pieces of Beech furniture, for all desks with a width of 120 etc.

  • Create a separate table for each set of related data and Identify each set of related data with a primary key

Example:

In a general Invoicing table you would have a separate table for Customers, Orders, Products, Invoices and you would probably need tables for OrderDetails and InvoiceDetails as well. Each of these tables must have their own primary key. Each of these tables except for customers would have a foreign key reference to the primary key of another table. (See Relationships below)

  • Do not use multiple fields in a single table to store similar data

Example:
(Underlined fields are Primary Keys and Italicised fields are Foreign Keys)

In a customer order you could have more than one product. That is the customer has ordered more than one item. If you tried to put all of this in one table as {OrderID, CustomerID, OrderDate, Product1, Product2, Product3} what would happen if the customer ordered more than 3 products. There would also be implications for querying the kind or quantiy of products ordered by a customer. Therefore these product fields don’t belong in the order table which is why we would have an OrderDetails table which would have a foreign key refernce to the Orders table {OrderDetailsID, OrderID, ProductID, Quantity}. Using productID as a foreign key to the product table means you don’t have to identify the product attributes here. This also allows you to enter a quantity figure for the product ordered.

Relationships:

All tables should have a 1 to 1 or 1 to many relationship. This means for example that 1 customer can have 1 or many orders and 1 order can have 1 or many details.

Therefore Orders table would have a foreign key reference to the Customer table primary key {OrderID, CustomerID, OrderDate} and the OrderDetails table would have a foreign key reference to the Order table primary key {OrderDetailsID, OrderID, ProductID, Quantity}. This table also contains a foreign key reference to the Products table. As a product is likely to be ordered more than once there is a many to 1 relationship between the OrderDetails and the Products table.