10 Useful articles about Database design

This is a list of ten useful articles about database design which includes some tips and practical suggests to help you to design quickly databases for your web projects.

The list includes some tips to define relationships-entities model, common database design mistakes, database normalization, how to use PHP and SQL to create tables and relationships and a correct approach to define relationships between tables.

1. Ten Common Database Design Mistakes
By Louis Davidson - No list of mistakes is ever going to be exhaustive. People (myself included) do a lot of really stupid things, at times, in the name of "getting it done." This list simply reflects the database design mistakes that are currently on my mind, or in some cases, constantly on my mind.
Read More...

2. Define relationships-entities model (tables, attributes, and relationships)
This post explains step-by-step how to design a typical database relationships-entities model.
Read More...

3. Database Design and Modeling Fundamentals
This article covers the basics of database design including normalization, relationships and cardinality. A great tutorial on database design.
Read More...

4. Create tables and relationships with SQL
A simple tutorial which illustrates how to implement a database using SQL and phpMyAdmin.
Read More...

5. Database Normalization and Table structures
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).
Read More...

6. How to use PHP and SQL to create tables and relationships
A simple tutorial which explains how to use PHP and SQL to create quickly tables and relatioships.
Read More...

7. Table and Database Operations in PHP
In this article by Marc Delisle, we will learn how to perform some operations that influence tables or databases as a whole. We will cover table attributes and how to modify them, and also discuss multi-table operations.
Read More...

8. Practical database design, normalization, history, and event logging
What are the best choices when designing the schema for a relational database? What is the rationale in deciding in favor of one and against some other alternative? Given the amount of vendor-specific recommendations, it is all too easy to overlook basic relational database fundamentals. In this second of two parts, author Philipp K. Janert presents an introduction to database normalization and to the five Normal Forms. He also discusses other possible uses for a database in a project, for example as a repository for configurational or logging data.
Read More...

9. Relational Database Design
Many web designers understand what a database is. Some have even written queries to access a database or even designed their own database. Unfortunately, many web designers suffer from unnecessary problems and setbacks because they do not understand the basics of relational database design. This tutorial teaches the basics of relational database design.
Read More...

10. A correct approach to define relationships between tables
This post explains how to adopt a correct approach to define database relationships.
Read More...

If you want to suggest other interesting links add a comment! Thanks!

Read More

Google Spreadsheets Tips: Add custom charts

In the past months I dedicated several post about Google Spreadsheets about how to use basic formulas, how to design a project plan structure and how to implement a gantt charts using Widgets. Yesterday my friend Ivan ask to me to dedicate a post about how to add custom charts and, in particular, dynamic charts which update themselves when an user update a table with input data.

This tutorial explains how to add custom charts on Google Spreadsheets using Widgets. If you are an Excel user you'll find this very simple!

Take a look at this spreadsheet or copy this spreadsheet in your Google Apps Account.


1. From table to chart
Image to have this table with the following data about a list of products:

For each product I want to display, in the same chart, price, cost and the difference between price and cost. Ho can I do? A good way to do it is using an Area Chart.

2. Select Chart
To add an Area Chart on Google Spreadsheets click on Insert gadget and from the Gadget window select Charts > Area Chart. The chart will be added on you active sheet. Now you have to set all required parameters (Range is necessary!):

If you take a look at the table at the step 1, cost and price values are in the column D and E, so the range is:

Sheet1!D3:E10

...where Sheet1 is the name of the sheet where is the table. Now click on Apply and Close to add your chart on the current sheet. Simple, no?


3. Dynamic Charts
Now, image to have the following table which simulate the selling trend of a set of products (products details at step 1). When an user change quantity (column K) total values in the table will be updated with the new data. Price is taken from the table at the step 1 (column E), using Vertical Lookup function (Vlookup) using the ID Product (column I) as search criteria.

Total (column J) is calculated as Quantity * Price. I want to add a chart which display how revenues are allocate (for each product) on the total revenue. You can do it easy using a Pie Chart. Click on Insert gadget link and from the Gadget window select Charts > Pie Chart. The chart will be added on you active sheet. Now you have to set all required parameters (Range is necessary!):

If you take a look at the simulation table, total values are in the column J and products names in the column I, so the range is:

Sheet1!I4:J6

...where Sheet1 is the name of the sheet where is the table. Now click on Apply and Close to add your chart on the current sheet. At this point every times you change quantity values (column K) your chart will be updated with the new data.

Take a look at this spreadsheet or copy this spreadsheet in your Google Apps Account.


Related content
Google Spreadsheets Tips: Gantt Chart (Microsoft Project-like) using Widget
Use Google Spreadsheets to get financial informations about companies
Gantt Chart using Google Spreadsheets and conditional formatting
Google Spreadsheets: formulas tutorial

Read More

Some updates about "Woork", The Daily Inspired

Hello friends! Some news from Woork (now "The Daily Inspired"). I spent the past two days to improve my blog and release this new template. I think now it's simpler to browse and more pleasant to read.

I increased font size, changed my home page and search results page. Let me know what you think about it! In the next days I'll release this template for free for all Blogger user. Stay tuned!

The new Home Page
How you can see, I added a "static" section with a little introduction about my blog and three sub sections (Blogger Template, Tutorials, Advertising).

Below there is "What's news" section with a list of my 10 latest post published on woork.

Improved search results
Now search results display only the post title, number of comments, and delicious link:

I increased to 10 the number of results displayed on the page and I removed post text.


Unlimited Bandwidth for my Box.net Account
In the past days I received a big amount of traffic on my Blog and I had some bandwidth exceeded problems with my Box.net account. Some readers noticed me which was impossible to download my tutorials. So I upgraded my Box.net account and now I have unlimited bandwidth at disposition and direct links to download all resources I upload on my account.

If you have some suggest to improve this layout add a comment. Thanks!

Read More

MooTools Basic Tips (lesson 3): interaction with HTML Form

This post is the third lesson which I dedicate to MooTools for newbie and illustrates how to implement simple interactions with form input elements using this powerful framework and unobtrusive JavaScript code.

Some time ago I wrote this post where I explained some useful tips to enrich HTML Form using simple JavaScript functions. Some readers asked to me to illustrate similar examples using MooTools and unobtrusive JavaScript code, so in this tutorial I implemented the Twitter-like chars counter example of my previous post. This counter decreases, from the max value of available chars in the input field (20 in this example) to zero, while you type something into the input field:

Click on the link below to download the full code.


Download full code


1. Add MooTools framework on your page
First, you may download the latest version of MooTools and add a link to the framework within the tag <head> of the page:

<script type="text/javascript" src="mootools.svn.js"></script>

2. HTML Code
HTML code for this example is very simple:

<label for="myInput">Write something here:</label>
<input type="text" id="myInput" maxlength="20" />
<span id="counter_number" class="counter">20</span> Remaining chars

In this code I added an input text field with id="myInput" and a span element with id="counter_number" which display the remaining chars.


3. JavaScript Code
Copy and paste the following code after MooTools link (see step 1) in the tag <head>:

<script type="text/javascript">
window.addEvent('domready', function() {

$('myInput').addEvent('keyup', function() {

max_chars = 20;
/* get the current value of the input field */
current_value = $('myInput').value;
/* get current value lenght */
current_length = current_value.length;
/* calculate remaining chars */
remaining_chars = max_chars-current_length;
$('counter_number').innerHTML = remaining_chars;

});

});
</script>

If you want, with some lines of additional code, you can change the counter text color for example from gray to red when remaining chars are less then 6:

This is the code:

<script type="text/javascript">
window.addEvent('domready', function() {

$('myInput').addEvent('keyup', function() {

max_chars = 20;
/* get the current value of the input field */
current_value = $('myInput').value;
/* get current value lenght */
current_length = current_value.length;
/* calculate remaining chars */
remaining_chars = max_chars-current_length;
$('counter_number').innerHTML = remaining_chars;

/* Change color if remaining chars are LT 6*/
if(remaining_chars<=5){
$('counter_number').setStyle('color', '#990000');
} else {
$('counter_number').setStyle('color', '#666666');
}

});

});
</script>

I added an if statement which checks the value of remaining_chars var. If this value is less then 6, this script change the counter text color to the value #99000, else to #666666.

Naturally this is only a very simple example which helps all MooTools newbie to learn the framework bases.

For questions please add a comment!

Download this tutorial

 

Read More

"Woork" in progress...

Hello guys... How you can see I'm working at a new version of this blog. Please have patience!
After a lot of request, I decided to release my new Blogger Template for free for all my readers.

Stay tuned!

Read More

10 Beautiful icons set for web developers and designers

This post contains a collection of 10 beautiful and very popular icons set useful to be used in your web applications or your websites.

1. Web development icons Download
Web development icons is a set with 60 free icons in web 2.0 style (24x24). These icons are free to use in any kind of project unlimited times.

2. Function free icon set Download
A great collection designed by Liam McKay. All icons are free for personal non-commercial use:

3. Pixelicious Download
150 beautiful pixel icons designed by PixelResort:

4. Social Web Button Download
Another great collection of social icons set provided from WebFruits:

5. Sweetie Pack Download
Sweetie Pack is a popular clean and beautiful icons set with other 100 icons. I suggest you to read this post. CSS Message Box collection with sweetie pack:

6. FeedIcons Download
This is a beautiful icon set feed

7. Mini Icons Download
Super mini icons set for web applications designed by FamFamFam:

8. Tango Icons Set Download
The Tango icon library contains a basic set of icons for the most common usage. To make it easier for a developer to find an appropriate icon for their application a number of aids are there to help.

9. Monofactor Download
Monofactor is a beautifu set contains 25 scalable Illustrator format icons:

10. Aquaticus Social Icons Download
Aquaticus is a beautiful social icons set designed by Jwoloh (60x60, 48x48, 24x24, 16x16):

If you have other icons set to suggest add a comment with the related link!

Related Content
Beautiful CSS buttons with icon set

Read More

Step by step guide for newbie to design a simple web application (part 1)

Design a web application requires hard work and knowledge of HTML, CSS, database and javascript. What is a simple way to start developing PHP web applications for a newbie?

In the past months I received frequently this question. Some readers of this blog often asked to me to publish a "newbie-oriented" post about how to create a web application using PHP. With this step-by-step guide I want to illustrate some basic tips for newbie to design their first (really simple!) web application using PHP, MySQL, Ajax features and MooTools.

About the application
What kind of application we'll develop in this tutorial? I chose a simple to do list application to manage a list of tasks with these basic features:

- insert task
- mark a task as completed
- remove task
- search task

In this first part, I'll explain how to:

- define application structure
- create a new database for your application
- create database tables and relationships
- setup the application home page

Are you ready? Let's go!


1. Application structure
First question is: how to organize our application? We can start using this simple structure which will be modified in the next steps adding new elements (folders and files):

The main folder todo-list will contain all application files and folders; db folder will contains all files to create and manage application database. connection.php enstablishes a database connection in all pages which require interaction with data stored into our database (see step 2.3).


2.1 - Database: create a new DB
Second step:create a new database for our application to store tasks information. We can use phpMyAdmin to create a new DB:

You have to add a name (in this case todo-list) in the input field and click on create button. Your new database will appear on the left panel: the number (0) indicates your database is empty (no tables).

Note: if you are a Mac user I suggest you to take a look at MAMP to configure PHP+MySQL environment on your system. For more info about MAMP read this post.


2.2 - Database: define DB tables
At this point, we have to define database tables. In our simple to do list application we have only a table TASK with these attributes:

Now we have to create this table into our new database. In general, you can create database tables and relationship directly using phpMyAdmin interface or writing SQL code in a separated PHP file ( tables_structure.php). I prefer to use the second option because it's simpler to manage. So, open tables_structure.php and copy and past this code:

<?php
//Database Connection
include('connection.php');

// Create table TASK
$sql="CREATE TABLE TASK (
task_id_pk INT NOT NULL AUTO_INCREMENT,
task_name VARCHAR(250),
task_description LONGTEXT,
task_completed INT, PRIMARY KEY (task_id_pk)
) TYPE=INNODB";
mysql_query($sql);

// Close DB Connection
mysql_close($db_con);
?>

In the first row I included the following line:

//Database Connection
include('connection.php');

...which enstablishes a database connection (see next step). So I added a PHP var $sql which contains SQL code to create a new table "Task". This code:

mysql_query($sql);

...executes a query with SQL code declared into $sql var.

Note: For more info about how to design a more complex database for your web applications (with relationships between tables) take a look at the following links:
Define relationships-entities model (tables, attributes, and relationships)
Create tables and relationships with SQL
How to use PHP and SQL to create tables and relationships
A correct approach to define relationships between database tables


2.3 - Database: connection.php
Enstablishing a database connection using PHP is very simple. You can use the following code only changing connection parameters values ($db_host, $db_name, $username, $password):

<?php
// Connection Parameters
$db_host="localhost";
$db_name="todo-list";
$username="root";
$password="root";
$db_con=mysql_connect($db_host,$username,$password);
$connection_string=mysql_select_db($db_name);

// Connection
mysql_connect($db_host,$username,$password);
mysql_select_db($db_name);
?>

In general $db_host, $username, $password don't require changes. This var:

$db_name="todo-list";

...contains the name of database we created at step 2.1.

Note: you have to include connection.php in each page which uses interaction with database.


2.4 - Database: create tables
Ok, now you can publish tables_structure.php and connection.php on your testing server and load tables_structure.php with your browser to create tables and relationships (in this case tables_structure.php will create only the table TASK ):

If code it's ok, using phpMyAdmin, you can find the new table "TASK" into our todo-list database:

3.1 - Index.php: include connection.php
Open index.php and add this line of code at the beginning of the page to enstablish a database connection:

<?php include('db/connection.php'); ?>

Remember, you have to include this file in each page which interacts with database:

3.2 - Index.php: include MooTools framewoork
How I said, we'll use MooTools framework to add moder ajax interactions to our application. So we have to add a link to MooTools framework into index.php simply copying the following code within the <head> tag of the page:

<script type="text/jscript" src="mootools.svn.js"></script>

3.3 - Index.php: include CSS file
Now, create an external CSS file style.css in a new folder CSS:

This file will contains CSS code to design application interface elements. So, in the <head;> tag add a link at this CSS file:

<link href="css/style.css" rel="stylesheet" type="text/css" />

Ok, at this point we are ready to design application interface and implement application features. In the next post we'll see how to insert new tasks, mark a task as completed, delete tasks, search tasks and how to design a simple application interface.

Read More

7 Stunning tutorials to design modern interfaces for websites

Are you looking for some ideas to design an original interface for your next website project using the most popular JavaScript Frameworks such as Prototype, MooTools and Scriptaculous?

Take a look at this short compilation which includes a Facebook-style lightbox; a nice interface library to develope advanced interfaces for web applications, web sites and widgets; a gallery/slideshow system which simulate Flash animation; an image slider to create Flickr-like slideshow; an advanced image gallery to design Apple-like images galleries; an ajax based slider and a ligtbox.

1. FaceBoxFacebox is a jQuery-based, Facebook-style lightbox which can display images, divs, or entire remote pages.
Read more...

2. MochauiMochaUI is a beautiful web applications user interface library built on the Mootools JavaScript framework to develope quickly Web Applications, Web Desktops, Web Sites, Widgets, Standalone Windows and Modal Dialogs.
Read more...

3. SmoothGallery 2.0 This javascript gallery and slideshow system (using mootools v1.11), allows you to have simple and smooth (cross-fading...) image galleries, slideshows, showcases and other cool stuff on your website. Absolutely Awesome!
Read more...

4. MooSlide revamp slider Revamp of mooSlideBox is an ajax based slider easier to use than ever before, as it now has become a class. There are some options that can influence the look and behaviour of your box. Of course you can also have more than one box on your page! In addition you can now define, from where the box should fly in: from top or from bottom ( sample links show both variants ).
Read more...

5. MooFlow Mooflow is an advanced image gallery with the following features:autoresize on changes of window dimensions, fullscreen option, scrolling with mouse wheel, scrolling with key input (left and right cursor), autoplay presentation loop, reflections via javascript, load images via json (with event or on startup), load images from a html-source with selector-filter, creates the UI automatically, UI skinable via CSS,fullsize viewer option, can run multiple instances and more.
Read more...

6. Simple images slider to create Flickr-like slideshows If you like Flickr slideshow, this article explains how to implement it using Prototype-UI framework.
Read more...

7. Slimbox, the ultimate lightweight Lightbox clone Slimbox is a 4kb visual clone of the popular Lightbox JS v2.0 by Lokesh Dhakar, written using the ultra compact mootools framework. It was designed to be small, efficient, more convenient and 100% compatible with the original Lightbox v2.
Read more...

Read More

7 Tips to design professional blog layout using Blogger

Blogger it's a very powerful platform to publish your blog. It's free and with some features which you can customize how you prefer, without limits.

In this post I want to illustrate some tips which can help you to improve your Blogger template to design a professional layout for your blog.


1. Start creating a custom template
A nice template is important for the success of your blog in terms of visits and return of money using AdSense. Before to start your custom template, writing code from zero, I suggest you to download a simple basic Blogger template, for example "Minima": so, copy and past on your preferred HTML editor HTML source (remeber to check "expand widget models" option) and remove everything is contained within b:skin tag. This is the only code which have to remain:

<b:skin> /*</b:skin>

...and, below the previous line, add this code into the <head> tag to create your custom CSS style. I suggest you to use the following CSS structure:

<style type="text/css">
/* -- 1. Standard HTML elements reset --*/
/* -- 2. Custom Page Elements --*/
/* -- 3. Redefine blogger standard elements --*/
/* -- 4. Other custom classes --*/
</style>

In this way CSS code will be simpler to manage and update. This is a screenshot of a part of CSS code I used to design my blog:

At this point you can start writing HTML code, adding layer, sections and other elements to your template. When your template is ready, copy the code and paste it on Blogger Layout editor and save it. If you need more info about Blogger customization, I suggest you to take a look at BloggerBuster a "Blogger-dedicated" site mantained for my friend Amanda which provides useful templates, tips and tricks for Blogger users.


2. Create single custom pages on Blogger
Using Blogger you can create single custom pages like on WordPress. How do you do it? Simply creating a post which you will use like a web page for example adding links or in general contents organized for category. On my blog I created some pages (Tutorials, Showcases, Freebies) and, for each page, I added a link on navigation bar:

...and each page contains links organized for topic (take a look at the page Tutorials). When you have to modify the content of the pages you have simply modify the related post directly from Blogger Post editor.


3. Display some HTML elements only on home page
Sometimes it's usefult to display some page elements only on home page and not on each single post. To do it, you have to use Blogger <b:if> statement, adding the following code on your Blogger template:

<b:if cond='data:blog.pageType != "item"'>
<!-- ... HTML code if you want to display only on home page here... -->
</b:if&gt

In this way, if the current page isn't an "item" page (but for example it's your home page) , will be displayed all code within <b:if> tag.


5. Page title and meta tag description
For a better indexing of your blog on Google I suggest you to make these simple changes on your template. Change this line (blog title):

<title><data:blog.pageTitle/></title>

...with the following code:

<b:if cond='data:blog.pageType != "item"'>
<title>My site | This is a short description</title>
<b:else/>
<title><data:blog.pageTitle/></title>
</b:if>

...and add Meta tag description below the previous <b:if> statement.

<b:if cond='data:blog.pageType != "item"'>
<meta name="Description" content="Add here a short description of your site" />
</b:if>

For example on my template I added: "Woork is a popular tech blog mantained by from Antonio Lupetti. Topics focus on web design, tutorials, resources and inspiration", so when someone looking for my blog on Google, search results display the following description:

4. Design custom categories section
By default, categories on Blogger are displayed on one column. If you want to modify the default layout showing labels on two columns instead one column, you have manage manually your labels using this simple trick: adding on your template, for each labels, a link like this:

<a href="http://yoursite.blogspot.com/search/label/Category Name?max-results=max numbers of posts to display">Category Name</a>

For example, to design my categories section I used this code:

<a href="http://woork.blogspot.com/search/label/ajax?max-results=2">Ajax</a>
<a href="http://woork.blogspot.com/search/label/blogger?max-results=2">Blogger</a>
<a href="http://woork.blogspot.com/search/label/coldfusion?max-results=2">Coldfusion</a>
...

...and how you can see on my sidebar the result is:

I know, the process is not automatic and each time you add new categories, you have to udate your template manually... but this solutions renders more flexible template design.


5. Republish Feed RSS to manage recent entries and comments
Personally I don't like Blogger default archive widget. So, on my template I used FeedBurner BuzzBoost service to add a section with recent entries and recent comments. If you have a FeedBurner account login, clik on "Publicize" tab and select "BuzzBoost" service:

Follow instructions on the page and copy and paste BuzzBoost code on your Blogger template, within a new layer with class property = "recentPost" (you can insert this layer, for example, within sidebar):

<div class="recentPost">

/* FeedBurner BuzzBoost Code*/
<script src="http://feeds.feedburner.com/your feed here?format=sigpro" type="text/javascript" ></script><noscript><p>Subscribe to RSS headline updates from: <a href="http://feeds.feedburner.com/Woork"></a><br/>Powered by FeedBurner</p> </noscript>

</div>

To customize recent entries section add a CSS class (recentPost) using, for example, the following code:

.recentPost li{background:url(arrow image URL) no-repeat left top;
padding-left:14px;}

Final result is something like this:

You can repeat the same process to add a new section which contains links to recent comments, simply changing the feed URL.


6. Add custom subtitle to your posts
If you want to add a subtitle to your Blogger post you can use a simple "trick" using CSS. On your Blogger template, create a custom class ".subtitle" (for example, I used this code for Woork's subtitles style):

.subtitle{

font-family:Georgia, "Times New Roman", Times, serif;
font-size:20px;
color:#555555;
line-height:28px;

}

Now, when you add a new post, to add a subtitle, add a div layer with class property = "subtitle" before the post content:

<div class="subtitle">This is a subtitle</div>

This is a screenshot of Blogger Post Editor:

So, add the rest of your post below the div layer and the result is:

7. Add social web buttons
Social web buttons are a extraordinary solution to share and make popular content you write on your blog. Delicious, StumbleUpon and Digg are my preferred social network which drive on my site a lot of traffic but also Mixx, Reddit, Yahoo Buzz!, DZone can help you to increase your visitors. For more info about how to add social buttons on your site take a look at this post for the full list.


Related content
Some Blogger Tips you probably don't know
Blogger Hack: add social buttons to increase your traffic

Read More