Science Library - free educational site

Database design and management

PHP is a server-side scripting language, and is ideal for operating a database on a server.

The database may be accessed directly and manually edited. However, a script on a website needs a server-side scripting language to perform these tasks remotely. PHP therefore works beautifully with MySQL to access, update and edit the information in the database.

Database design needs to be done well if the power of PHP is to come to the fore. Well-structured and normalised tables, logically interconnected through primary and foreign keys, ensure an open-ended and sustainable structure to the database that can operate at any scale.

Databse relations
Database tables need to be normalised to be efficient

Relational Database Management refers to the type of relationships between data in separate tables. In the example, a bookshop wishes to create a database of its stock. It could create a single table, listing all the information about a book in each row (record). The rectangles represent table names, and the ovals column names. The lines between the table names indicate whether the relationships are one-to-one or many-to-one.

However, what if someone asked if there were any other books by the same author? Or the accounts department needed all the books from a single publisher, or date of publication. Or perhaps only the ISBN number.

Keys

The relationships within and between tables are indexed by keys. There are two types of key needed to make a relational database function:

Primary Key

The primary key is how the data in a table is primarily ordered. This is ideally an integer, since MySQL is far more efficient with integers as indexes. Table columns which are intended for primary keys can be set to 'auto-increment', so that the table is filled sequentially, starting at '1'. However, primary keys could also be any of the data types that are suitable.

Rules: a primary key must comply to three requirements:

  1. PKs cannot be NULL (i.e. must always have a value)
  2. Once set, a record cannot change its PK (which would be like pulling out a thread in a complex weave)
  3. Be unique for each record
Foreign Key
  1. FKs would usually not be NULL (i.e. if it is not present, the table still works, but is no longer referencing the external table: whether this is a problem or not depends on the function of the database)
  2. FKs can be changed (and added as an afterthought)
  3. Do not have to be unique for each record

The foreign key is a column in one table that refers to a primary key of another table. For example, the auth_id in the example of the bookstore inventory database is a foreign key for the titles table. The FK links a unique title to its author, not by her name, but the id number she has in the author's table. This helps to avoid ambiguity of shared names.

In the example, the authors has a one-to-many relationship with the titles table. In reality, a book may have more than one author, but for the sake of the exercise, we are assuming the bookstore catalogues books by the first author name only. Each author is unique, and each title has a direct, single relationship to one author. However, an author may have more than one title. The primary key in authors is the auth_id, and this is used in the titles table as the foreign key. FKs can therefore be repeated in a table (an author's ID appears for each title she has published).

Normalization

Normalization refers to the manner in which data is organised. Relational databases work by the relationships between the data in different tables.

First Normal form
  1. Each column contains only one value (aka. atomic or indivisible)
  2. Data of a similar type is not repeated in a table
  3. Columns which are NULL too often indicates a poorly designed database
Second Normal form
  1. All data is first put in 1NF
  2. Data that is repeated is placed in its own table, and linked to the first table by a primary or foreign key
  3. Creating separate tables for similar types of data removes limitations on the non-dedicated table
  4. One-to-many relationships are good, many-to-many relationships are bad

First normalization means that a data of a similar type is uniquely listed in a single table. In the authors table each author appears once, even though she may have written more than one book. A non-normalized database would have the author's full details in the same table as a book title and details. This would oblige there to be either multiple repetitions of either book title etc. columns, or multiple occurrences of the author's details.

Instead, all of the author's details are listed once in a table dedicated to that purpose, and any requirement for information from that table in other tables is achieved by a single, unique primary key reference (auth_id).

Not only does this make data retrieval more efficient, it permits data, such as an author's telephone number, to be updated at any point in time with a single action, with no risk of redundant data creating conflicts throughout a complex database.

A third normalization procedure would ensure that no data in a table is interdependent. This means that every column would be directly dependent on the primary key. In practice, this may be excessively devisive, creating many tables which in themselves becomes difficult to keep track of. To what degree a database is normalized depends on how large and complex the data set is, and how the information is to be retrieved and edited.

Table Types

There are several different table type, or storage engine, in the MySQL database application. They have varying characteristics, and perform differently for different purposes.

The most common storage engine is MyISAM. This table type is very useful and flexible, with the singular drawback of not being usable for transactions, for which the alternative InnoDB may be used.

InnoDB requires more disk space and is generally slower than MyISAM. In addition, it does not support FULLTEXT indices.

Importing data from a database

A typical interface between PHP and MySQL is accessing a database hosted on a server, running a query, and placing the records found in a useful format, such as a single or multi-dimensional array.

mysqli_connect

The first step is to access the database. For this we need to know the access data for the database:


DEFINE ('DB_USER', 'user_name');
DEFINE ('DB_PASSWORD', 'secret_password');
DEFINE ('DB_HOST', 'name_of_host'); 
DEFINE ('DB_NAME', 'database_name');

// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) 
OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );

Since this is for-your-eyes-only data, it should be kept in a file called something like mysqli_connect.php outside the root of the domain on the server. This prevents anyone else finding the file and exploiting the data for nefarious purposes.

config.php

The call to this file can be made ad-hoc when needed, but to ensure access to the mysqli_connect file from anywhere on the site, you can create a config.php file to store its URL, as well as useful constants, and include the config file on each page, whether the database is needed or not.


define('LIVE', TRUE); /*this allows you to change the 
                       behaviour of a site in development */
define('EMAIL', 'webmaster@zumguy.com'); 
              /*sets an email as a constant for use anywhere needed */
define('BASE_URL', 'http://www.sciencelibrary.info/'); 
              /*sets a URL base for absolute URL references */
define('MYSQL', '../../mysqli_connect.php'); 
              /*sets the path to the top-secret database access data file */

Database access query

Once the database has been successfully accessed, a query may be run to retrieve data from a table or a number of tables.

A good way to store the data returned by the query is to create an array, which is structured to make the data useful and logically ordered.


require_once(MYSQL);
    /* this is a constant set in the config.php file */

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  } else { //only attempt this query and code 
           if database connection established

$q = "SELECT * FROM table";
$r = @mysqli_query($dbc, $q); 
	if (mysqli_num_rows($r) > 0) { 
		while($s = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
		$new_data_array[] = $s;
		}
	} else {
		return FALSE;
	}

  } 

Date and Time Insert and Retrieval

A database stores the date and time in a format which needs to be converted for use on a website. To instruct the computer to insert today's date in a column updated:

$temp = new DateTime(); 
$data['updated'] = $temp->format('Y-m-d');
// 'Y-m-d H:i:s' will include the time
$timestamp = strtotime($your_table_data['date']); 
$date = date('F j, Y', $timestamp);
// returns June 14, 2015

This returns the stored date value from the data base and converts it from a string to time format in the variable timestamp, which can then be displayed in any way desirable.

Be careful of the American order of days and months!

$date = date('Y-m-d', strtotime('06/14/2015'));
// returns 2015-06-14

Content © Renewable-Media.com. All rights reserved. Created : September 19, 2014 Last updated :February 14, 2016

Latest Item on Science Library:

The most recent article is:

Trigonometry

View this item in the topic:

Vectors and Trigonometry

and many more articles in the subject:

Subject of the Week

Mathematics

Mathematics is the most important tool of science. The quest to understand the world and the universe using mathematics is as old as civilisation, and has led to the science and technology of today. Learn about the techniques and history of mathematics on ScienceLibrary.info.

Mathematics

Great Scientists

Jean-Baptiste Lamarck

1744 - 1829

Jean-Baptiste de Lamarck was a prominent French botanist and invertebrate zoologist, who developed a transmutation theory of change in species, which became the major fore-runner to the Darwinian theory of Natural Selection.

Jean-Baptiste de Lamarck, 1744 - 1829
SaraOrdine

Quote of the day...

When I said I wanted to be a comedian, they laughed. Well, they're not laughing now...

ZumGuy Internet Promotions

Transalpine traduzioni