Posted on by Pam Van Londen

Database Design

Readings

SQLCourse.com
Free interactive course online, for learning MySQL.
MySQL tutorial
MySQL Reference manual, chapter 3.
Database Enabled Websites
Tutorial recommended by OSU PHP documentation.

A database is a collection of related data. It can also be viewed as a collection of related tables.

Tables have sets of fields. Field types define the kind of data the field can contain, including:

String
Stores text and numerical characters.
Numeric
Stores numbers that can be calculated.
Date and time
Stores dates and times.
Boolean, or logical
Accepts one of only two specified values.

It is possible to create tables; define fields; and enter, query, and display data using scripts in web pages. For this course, we will use three “tools” to do this:

MySQL
An open-source database residing on the ONID server; each student gets one.
phpMyAdmin
The interface provided to create tables, define fields, enter, browse, test, and query data.
PHP scripts
In web pages, to enter, query, and display data from database tables.

MySQL database via your web server account

MySQL is an open-source database. Most web server accounts include the use of at least one MySQL database. To design a database, think about data you would like collected from your visitors. What do you want to know about them?

Start with pen and paper to list the ideas about like sets of data you want to collect. Organize the sets into:

  • Tables (or entities)
  • Fields
  • Relationships (how one table uses data from another table)

For instance, if you just want to track inquiries, a single table might meet your needs. This database uses one table (contact) and that table uses 9 fields:

Inquiry database structure
Table NameFields needed
ContactCustomerNumber
FirstName
LastName
Address
City
State
Zip
Phone
Email

If you have more complicated data to collect, group related fields in separate tables. For instance, if you want to track statistics of your favorite sports teams you may end up with 4 tables, several fields per table, and relationships between fields/tables:

Team standings database structure
Table NameFieldsType
Teams Index
Rank
Name
Location
Sport		 Int
Int
Varchar
Varchar
Varchar
Varchar
Players & Coaches Index
TeamIndex
Rank
Name
Location
Age
Position
Sport
Comments		 Int
Int
Int
Varchar
Int
Varchar
Varchar
Varchar
Text
Sport SportType
SeasonStart
SeasonEnd		Varchar
Date
Date
Matches Index
Location
DateStart
DateEnd
Rank		Int
Varchar
Date
Date
Int

Once you decide the number of tables you need, and what you want to call each field, define the field types.  Basic types include:

Integer (int)
Numerals that will be calculated.
Float
A number with decimals.
Variable Character (varchar)
Numerals and text in short strings; usually 100 characters or less.
Text (text)
Numerals and text for long passages.
Date (date)
Years months, and days. The default is year-month-day.

Some attributes to note include:

Index
Provides a unique identifier for the row/record in the table. Can be the primary key field. Use “idx” rather than “index” for the field name; it is sometimes dangerous to use a field name that is already a standard database term/variable/type, etc.
Null
Means the field can remain empty; the visitor doesn’t have to fill this field in.
Not Null
Means the field cannot remain empty; the visitor must fill in the field.

When you know what fields you need, you can either define the tables and fields using:

  • phpMyAdmin
    • Link to it from your database set up page accessible when you login to your web server account.
    • Requires your database login and password.
  • PHP Scripts
    • Find or write a script that creates a tables and fields.

Once your fields are defined, build a web page form to collect the data. Use the same field names in your form as used in your database.

You’ll need snippets of code to:

  • Login/connect to the database
  • Add a row to the database
  • Add data to the row
  • Display the new row and/or a set of records/rows
  • Close the connection to the database

Larry Ullman, author of the Visual Quickstart Guides for PHP and MySQL, says “One of the best ways to determine what information should be stored in a database is to clarify what questions will be asked of it and what data would be included in the answers.”

See Displaying database content with MySQL queries for example code.