mySQL : 

Open Source Relational Database

mySQL is an an open source relational database that uses the Structured Query Language. Quite a mouthful:

• Open Source - it's available for free download, and is developed by a community of programmers who give away the source code that makes the software work.
• Relational Database - a kind of database that is capable of connecting different sets of related information. For example, one table in a database may have records for all VW cars, with fields for price, model name, description, etc. Another table may have records for all VW car parts with fields for part number, car model that uses this part, description, price, etc. A relation can be established between the two tables so that information for a specific car and only the parts for that car are displayed.
• Structured Query Language (SQL) - A standard language used to communicate, or make queries for most modern database systems. Used for s searching the database, as well as creating, modifying and deleting information from the database.

This section has the following topics:

• database design concepts
  • defining the database table(s) - determine the kind and format of the data for this application
  • overview of field types - tables are made up of fields of different types:
    • numbers
    • text/strings
    • dates/times
• building the database
  • creating the table(s)
    • access your database with phpMyAdmin
    • create the table
    • add the appropriate field types
  • add data using phpMyAdmin to help with testing

When creating a dynamic web site with a database, one of the first implementation tasks is to determine the kind of data you will be using and how you will store it. A good approach is to make a list of each bit of information that needs to be stored. What are the kinds of data types you will need to store this information? Then match your data types to the mySQL types listed below. It is always a good idea to use the data type that most closly matches your requirement. For example, using a BIGINT (which can hold a number more than a thousand, thousand trillion) for a number that will range from 1 - 100 is a waste of space.

Once you know the data types, see if all the information can be grouped into records that are all the same, or if different kinds of records are needed. Use the idea of a spreadsheet to think about organizing the information. Do you need one spreadsheet or more than one spreadsheet? Each spreadsheet is equivalent to a table in the database.

Criteria for defining a database:

• What are the "data types"? I.E. what types of information are you storing?
  • Numbers, text, dates or times?
  • And how big will these information records be?
  • If it's a number, what is the range of the number? 1-100 (TINYINT) or 1 - 1,000,000 (MEDIUMINT)?
    • Can the number be negative (UNSIGNED vs. SIGNED)?
    • Can the number have decimal fractions (FLOAT)?
  • If it's text, how many characters will it have maximum? 50 (VARCHAR)? 5000 (TEXT)?
    • Can the text be reduced to a fixed list of possibilities (ENUM or SET)
  • If it's a date or time, is it a combined element (DATETIME) or separate (DATE or TIME))
    
• Is there a unique field that can be used to identify each record in the database? You need some way of getting at a single record so you can make changes to only that record. This is often called a KEY or INDEX. A good example would be a student ID number because everyone's ID is different. A bad example would be last name, because there can be many duplicates. If your content doesn't have a field that will always be unique, then you can create a special field just for this purpose and let the database create the unique INDEX (in mySQL use the auto increment feature).
  
• Can everything be stored in one table, or does the information need to be divided up into different groupings? This is often related to how the information is related. For example, the car database mentioned above would have perhaps 20 cars, but probably thousands of parts, some of which are shared between cars. In this case, one table would be created for listing the cars, and another table would be created to contain the car parts. This would avoid the repetition of listing every part in every car record.
  
• Are fields required or optional? When creating the database, you can require that a field value be entered (NOT NULL) or not (NULL).

Fields (which are also called Columns in mySQL) are the basic unit of information in a database. The field type determines what kind of information can be stored in the database. It's beyond the scope of this course to go into all of the possible field types. For complete documentation, see the books recommended on the previous web page, or go to the official mySQL site at:

www.mysql.com/doc/en/Column_types.html

There are two main types of numbers you will work with, integers and floating point numbers. These numbers can be SIGNED or UNSIGNED, which means that they can represent negative numbers or not. If you don't need negative numbers, use UNSIGNED, which will double the highest number possible for a specific field type (e.g. using an UNSIGNED TINYINT give it a highest number of 255 instead of 127). In the version of mySQL we use floating point field types do not support the UNSIGNED type, but mySQL 4.02 and later does.

Integers - these are whole numbers that do not have any fractional parts. For example, 42, 1000002, -375. An integer field is a good way to store the count of something, a weight in ounces to the nearest ounce, or someone's age.

Some mySQL integer types, storage, and range:

• TINYINT - 1 byte - SIGNED -127 to 127, UNSIGNED 0 to 255
• SMALLINT 2 bytes - SIGNED -32,767 to 32,767, UNSIGNED 0 to 65535
• INT - 4 bytes - SIGNED -2,147,683,648 to 2,147,683,648, UNSIGNED 0 to 4,294,967,295

Integers can have the AUTO_INCREMENT attribute assigned to the field (only one field per table). If you use this, everytime a new record is added to the database, the field with the AUTO_INCREMENT will get a new unique number, next in the sequence.

Floating Point - these are numbers that have a fractional part. For example, 3.14159, -1000.96, 488.23E+4. These can be used for precise measurements in inches (e.g. 10.375"), scientific calculations, or money. Note that floating point numbers support the exponent to set the magnitude of the number. For example, 25.03E+3 equals 25,030 (i.e. 25.03 * (10 * 10 * 10)).

Some mySQL floating point types, storage, and range:

• DOUBLE [(M,D)] - 4 bytes - positive or negative values in the range of 2.2250738585072014E-308 to 1.7976931348623157E+308. The M is the display width (the number of digits) and D is the number of decimal places displayed.
• DECIMAL [(M,D)] - M+2 bytes - This format stores the number as text, so the precision depends directly on M (the number of digits), and D (the number of decimal places). This format is useful if you need to store exact numbers--for example storing money amounts that must be exact to the penny. The DOUBLE and FLOAT column types can be imprecise because of the way the numbers are stored.

Text, known as strings in the computer world, can be represented in a mySQL database in two basic ways. As a series of characters that makes up any kind of text, or as a list of pre-defined values. For example, normal text could be a last name "van Allen" (VARCHAR), or a whole page of text (TEXT). A list of pre-defined text values could be a list of states "CA", "OR", "WA", only one of which can be selected (ENUM), or a list of colors "red", "green", "blue", in which one or more could be selected (SET).

Some normal text types, storage, and range:

• VARCHAR(M) - Actual text + 1 byte - 255 characters maximum. If the text "two words" is stored, it will take up 10 bytes (9 characters including spaces plus one byte). M is the number of characters allowed. Use this format for short strings of text.
• TEXT - Actual text + 2 bytes - 65,535 characters maximum. Use this format for most text applications with more than 255 characters.
• MEDIUMTEXT - Actual text + 3 bytes - 16 meg. Use this only for very large text applications.

List text types, storage, and range:

• ENUM("value1", "value2", ...) - 1 or two bytes - 65,535 members. Use this format for lists where only one value can be selected.
• SET("value1", "value2", ...) - 1 to 8 bytes - 64 members. Use this format for lists where multiple values can be selected.

Dates and times can be stored individually such as 2003-06-02 (DATE year, month, day) or 13:00:00 (TIME hours, minutes, seconds) or can be stored together as a date/time combination 2003-06-02 13:00:00 (DATETIME).

Some date/time types, storage, and range:

• DATE - 3 bytes - 1000-01-01 to 9999-12-31. Dates can have zero values for month and day (e.g. to represent unknown values), and there is no checking for invalid dates such as February 31st. Dates can be entered in a variety of text and numeric formats. For example, "YYYYMMDD", "YY-MM-DD", YYYYMMDD (as a number) etc.
• TIME - 3 bytes -  -838:59:59 to 838:59:59. Time can represent elapsed time, which is why the large hour and negative values are allowed. Times can be entered in a variety of formats. For example, "hh:mm:ss", "hhmmss", or hhmmss (as a number).
• DATETIME - 8 bytes - 1000-01-01 00:00:00 to 9999-12-31 23:59:5. Values can be entered in a variety of formats. For example "YYYY-MM-DD hh:mm:ss", "YYYYMMDDhhmmss", or YYMMDDhhmmss (as a number).

Once you know what your field types will be, use phpMyAdmin to create your table.

To do this:

1. go the the MDP phpMyAdmin page: mysql.philvanallen.com
2. login with your last name (2 letter names need first initial first) and student id
3. select your database in the upper left hand corner of the interface
4. create a new table by entering the name and number of fields needed (be sure this number includes a unique key/index). Note that the table name must not have any spaces in it.
   
5. give names to each field and set the field type. note that it's a good idea to prefix every name in the table with a short prefix related to the function of the table. This is good documentation, and it prevents using a name of a field that is not allowed by mySQL (e.g. "key" and "order" are reserved for use by the query language, but hm_key or hm_order are not).
   
   Example of field definitions for a simple table used to populate a homepage
   
   click for full size image
   
   Example of field definitions for a table of links, where the link_active field is used to determine if that record will be shown on the website
   
   click for full size image
   
6. if the field needs any lengths or values defined, enter them in the corresponding column. If you have an ENUM or SET field, enter the values with each value in single quotes, separated by commas. E.g. 'good','bad','indifferent'. The default value for an ENUM or SET should be entered without quotes.
7. set a default value for any of the fields if desired.
8. if fields cannot be empty when data is entered (i.e. a value in the field is required), set the Null column to NOT NULL. Otherwise, set it to NULL.
9. after setting the options for every field, enter a comment that describes the table, and click on SAVE
10. if you make any mistakes, you can always make changes to the table by selecting the STRUCTURE option at the top of the page.

When you are developing a website, it's usually a good idea to put some data into the database so you can do testing, even before you've created pages that insert new data or update it.

To do this in phpMyAdmin, select the INSERT option at the top of the page. Enter data for as many records as needed.

all materials on this web site © copyright 2005, Philip van Allen