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!

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.

Database design fundamentals

This is a mini-guide with hot tutorials to learn, step-by-step, how to design a database from relationships-entities model to final implementation using SQL, MySQL and PHP.

If you are a newbie to design database you can find it very useful.

Database design
Define the 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

A correct approach to define relationships between database's tables

I dedicated already some post about how to design and develop a database, but some readers asked to me to explain how to define relationships in a database and a correct approach to create them.

In general a relationship is defined between a foreign key and a primary key of two - or more - different tables. An example can be the following: an user lives in a city (or in a city live more users). The relationship-entity model for this simple case is something like this:


Generally, to optimize the database, it's a good rule to define a relationship using two INTEGER type fields. For example if you have 3 cities into the CITY table identified from ID_CITY_PK (primary key):

ID_CITY_PK (1)
CITY (Paris)

ID_CITY_PK (2)
CITY (New York)

ID_CITY_PK (3)
CITY (Rome)

... and a record into the USER table with ID_CITY (foreign key) = 3, this relation associates the current record to the city with ID_CITY_PK = 3 into the CITY table, that is Rome.

ID_USER_PK (1)
NICK (Antonio)
...
ID_CITY (3)

How to define relationships using phpMyAdmin
If you use a database MySQL and phpMyAdmin to manage it, select a table (in this example USER) and click on "Relation View", below the table's fields:


Now, from the foreign key field in the table USER (id_city) the field with which you want define the relationship, in this case id_city_pk in the CITY table:


Click on save to save created relation and repeat the action for all tables require relations.
If you use a Database Access, relationships can be defined from relationship view simply connecting, just with a mouse's click, two field.


How to define relationships using SQL
I suggest to use always a separated file with SQL statements to create all database's tables and relationships between them. The code is very simple, easy to modify and to reuse. A relation is defined using the statement REFERENCES:

CREATE TABLE CITY (
id_city_pk INT NOT NULL AUTO_INCREMENT,
city VARCHAR(100),
PRIMARY KEY (id_city_pk)
) TYPE=INNODB;

CREATE TABLE USER (
id_user_pk INT NOT NULL AUTO_INCREMENT,
nick VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
id_city INT NOT NULL,
PRIMARY KEY (id_user_pk)
FOREIGN KEY (id_city) REFERENCES CITY(id_city_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;

Remeber the order is very important when you create your database: first, you have to create table without dependancies and then all others.


Related Posts

For other about this argoument visit the following links:
Project a database: define the relationships-entities model (tables, attributes, and relationships )
Project a database: create tables and relationships with SQL
Project a database: how to use PHP and SQL to create tables and relationships

... or take a look at the table of contents.

Populate a "select" form with a SQL query using coldfusion

In this post I replied to some reader that have asked to me how to populate a select form element with the results of a SQL query using PHP. I received the same question about how to do the same action using Coldfusion.

Image a simple SQL query which get all users' names on a generic database's table, in this case USER, which have two attributes: ID_USER_PK (primary key) and NAME (the user name):

<cfquery datasource="mydatasource" name="getUser">
SELECT * FROM USER
</cfquery>

Now, to display the query's results into a SELECT form you can use this simple code with <cfoutput query="queryName">:

<select name="select" name="userName">
<cfoutput query="getUser">
<option value="#id_user_pk#">#name#</option>
</cfoutput>
</select>

If you have a parameter in input, for example a URL variable, idUser:

#URL.idUser#

...passed to the page in this way:

http://localhost/index.php?idUser=1

<select name="select" name="userName">
<cfoutput query="getUser">
<option value="#id_user_pk#" <cfif #URL.idUser# EQ #id_user_pk#>selected="selected"</cfif>>#name#</option>
</cfoutput>

In this way, you force to set the "selected" attribute, in your SELECT form, equal to "selected" if the current record has an id_user_pk equal to the parameter in input.

Are you a PHP developer? Read here the PHP version of this post

Project a database: how to use PHP and SQL to create tables and relationships

This tutorial explains how to replicate in your remote server the database structure you created on your localhost.

A good way is to create a PHP file that executes SQL queries to create database tables and relationships. In this previous post we have seen how to define the relationships-entities model (tables, attributes, and relationships) for our database to be used in a simplified del.icio.us-like web application:

Step 1: add a DB folder into the site structure
In the site root we create a new folder DB and add a file create_database.php into this folder.



Step 2: create the php file
Open create_database.php on Dreamweaver and switch to Code View. Copy and paste this code inside the <body> tag.

<?php
//Connect to database
include('../config.php');

// CREATE TABLE USER

$sql= "CREATE TABLE USER (
id_user_pk INT NOT NULL AUTO_INCREMENT,
nick VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
user_reg_date DATE,
PRIMARY KEY (id_user_pk)
) TYPE=INNODB";
mysql_query($sql);

// CREATE TABLE SITE

$sql="CREATE TABLE SITE (
id_site_pk INT NOT NULL AUTO_INCREMENT,
site_url VARCHAR(250),
site_description LONGTEXT,
site_data_reg DATA,
PRIMARY KEY
) TYPE=INNODB";
mysql_query($sql);

// CREATE TABLE SHARE

$sql="CREATE TABLE SHARE (
id_share_pk INT NOT NULL AUTO_INCREMENT,
id_user INT NOT NULL,
id_site INT NOT NULL,
submitted_by INT NOT NULL DEFAULT 0,
PRIMARY KEY (id_share_pk),
FOREIGN KEY (id_user) REFERENCES USER(id_user_pk) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (id_site) REFERENCES SITE(id_site_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB";
mysql_query($sql);

// Close Connection
mysql_close($db_con);

?>

Now save the file and test it on localhost. Remember to include in create_database.php, connection's info (config.php) to your database.

In this way we can reuse create_database.php to copy your database structure on remote server when our project is ready to be published.

Project a database: create tables and relationships with SQL

In the previous lesson we have seen how to design the relationship-entities model for a database to be used in a del.icio.us-like web site project. Our R-E model is:



Now we implement the database using SQL and phpMyAdmin. We crate a new database on phpMyAdmin and select the "SQL" tab. Copy and paste this SQL code into the form and click on execute button:

CREATE TABLE USER (
id_user_pk INT NOT NULL AUTO_INCREMENT,
nick VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
user_reg_date DATE,
PRIMARY KEY (id_user_pk)
) TYPE=INNODB;

CREATE TABLE SITE (
id_site_pk INT NOT NULL AUTO_INCREMENT,
site_url VARCHAR(250),
site_description LONGTEXT,
site_data_reg DATA,
PRIMARY KEY
) TYPE=INNODB;

CREATE TABLE SHARE (
id_share_pk INT NOT NULL AUTO_INCREMENT,
id_user INT NOT NULL,
id_site INT NOT NULL,
submitted_by INT NOT NULL DEFAULT 0,
PRIMARY KEY (id_share_pk),
FOREIGN KEY (id_user) REFERENCES USER(id_user_pk) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (id_site) REFERENCES SITE(id_site_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;

Create Relationships
To create relationships between database's table (for example between SHARE table and the other tables) you have to use the SQL code below:

FOREIGN KEY (attribute_name_1) REFERENCES tableOfReference(attribute_name_2)

where attribute_name_1 is the foreign key (generally, a field of type INTEGER)a and attribute_name_2 the primary key of the table of destination.

To force the referencial integrity between the data of database, you have to add this code:

ON UPDATE CASCADE ON DELETE CASCADE

Our database is now ready and we can implement it using PHP and MySQL

Project a database: define relationships-entities model (tables, attributes, and relationships)

This lesson explains how to project a typical relationships-entities model for a database to be used in our web projects. My approach is:

1. 1. Define database entities (tables)
2. 2. Identify attributes for tables
3. 3. Define relationships and cardinality between the istances (records) of tables

Step 1: define database entities
The first step when you project a database is to identify all entities (tables). For example if we want to project a simplified del.icio.us-like web site, our database will have these entities:

1. - USER (to store data about users, email, password, nickname,...)
2. - SITE (to store data about the sites added by the users)

These are only the main entities required from our project but, take a mind, that we will add other tables to store data about relationships between istances (records) of these tables in case of cardinality (M:M), many to many (see Step 3).

Step 2: define attributes
The next step is to define attributes for the tables USER and SITE. In this semplified example we will have something like this:

USER
-----------
id_user_pk (Primary Key)
nick
email
password
user_data_reg (user signup date)


SITE
-----------
id_site_pk (Primary Key)
site_url
site_description
site_data_reg (when a site is added)
totale_share_user (total number of users that share a site)

Step 3: define database relationships
Our simple application del.icio.us-like works in this way: an user add a site that can be shared by other users. The relationship's cardinality beetwen USER table and SITE table is:

USER > SITE (M:M) - Many to Many (an user can add many sites).
SITE > USER (M:M) - Many to Many (a site can be shared by many users).

In this case ( cardinality M:M) we have to add a new table (SHARE) that contains all possible combinations between all instances of USER table and SITE table . In this new table, SHARE, to identify an user that share a site added by another user or by itself, we will add two Foreign Key:

SHARE
-----------
id_share_pk (Primary Key)
id_user (Foreign Key > USER)
id_site (Foreign Key >SITE)
submitted_by (boolean: flag only if the current user has submitted the site)

Implement your database using SQL
Now, our database is ready to be implement with a DBMS (for example using MySQL). The next lesson will explains how to implement this database using SQL language and phpMyAdmin.

Config.php: define your database connection's parameters

In this lesson we will create a new MySQL database using phpMyAdmin and a config.php file in the site root with connection's parameters for the database (database name, database host, username, password).



Open config.php and copy and paste the code replacing the value of variables $db_host, $db_name, $username, $password, with correct parameters.

<?php
// Connection's Parameters
$db_host="localhost";
$db_name="database_name";
$username="database_username";
$password="database_password";
$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);
?>

Now, in the site root, create index.php file and use PHP include() function to include config.php into the page.

<?php include('config.php') ?>

This is a simple way to enstablish a connection with your database MySQL to be used when you need to execute a SQL query.
The index.php structure will be explained in the next lessons.