Basic Database Information

Before I lead you down the path of database development, you should become familiar with the basic structure of a database. For some this may be an unnecessary review. However, as one of the strengths of PHP is its ability to interact with a database to create a truly dynamic environment, having a basic idea of database structure and theory will help you create even better overall applications. After learning about structure, you'll also learn a little bit about database normalization.

But back to the basics of a database:

  • A database is a collection of tables.

  • A table contains a set of records.

  • All records have the same number of fields.

  • Each field categorizes a piece of data.

Starting with the smallest element, suppose you have a few uncategorized, seemingly random pieces of data: Joe Smith, 1970-10-03, and blue. These pieces of data can be placed into categories such as Full Name, Birthday, and Favorite Color. Give these fields some database-friendly names, such as FULL_NAME, BIRTH_DATE and FAVE_COLOR.

Now visualize these fields horizontally as they become the labels for columns in a table:

 +-------------+---------------+--------------+ |  FULL_NAME  |  BIRTH_DATE   |  FAVE_COLOR  | +-------------+---------------+--------------+ 

To create the first record (row) in this table, enter Joe Smith's information. Your table then looks like this:

 +-------------+---------------+--------------+ |  FULL_NAME  |  BIRTH_DATE   |  FAVE_COLOR  | +-------------+---------------+--------------+ |  Joe Smith  |  1970-10-03   |  blue        | +-------------+---------------+--------------+ 

Add additional records to your table by adding more friends:

 +-------------+---------------+--------------+ |  FULL_NAME  |  BIRTH_DATE   |  FAVE_COLOR  | +-------------+---------------+--------------+ |  Joe Smith  |  1970-10-03   |  blue        | |  Mary Smith |  1962-07-25   |  red         | |  Jane Smith |  1968-04-28   |  black       | +-------------+---------------+--------------+ 

Your table gets its name when you initially create its structure, and you populate your table after it's created. You'll learn all about table creation a bit later, but for now just recognize that tables, records, and fields are the basic elements of a database.

The Importance of Unique Identifiers

The FRIEND_INFO table from the preceding section is missing something: a unique identifier. You might think that the FULL_NAME field is the unique identifier. If so, you better not have any additional friends named Joe Smith, Mary Smith, or Jane Smith. If FULL_NAME is the unique identifier, then no one else with those exact names can be included in your FRIEND_INFO table.

Instead, how about a simple FRIEND_ID field? If you have two friends named Joe Smith, this ensures that you'll know their favorite color when it's time to get them a birthday present.

Add the FRIEND_ID field to your FRIEND_INFO table:

 +-------------+--------------+--------------+--------------+ +  FRIEND_ID  |  FULL_NAME   |  BIRTH_DATE  |  FAVE_COLOR  | +-------------+--------------+--------------+--------------+ +  1          |  Joe Smith   |  1970-10-03  |  blue        | +  2          |  Mary Smith  |  1962-07-25  |  red         | +  3          |  Jane Smith  |  1968-04-28  |  black       | +-------------+--------------+--------------+--------------+ 

You can now add your other friend named Joe Smith. The FRIEND_INFO table will now look like this:

 +-------------+--------------+--------------+--------------+ +  FRIEND_ID  |  FULL_NAME   |  BIRTH_DATE  |  FAVE_COLOR  | +-------------+--------------+--------------+--------------+ +  1          |  Joe Smith   |  1970-10-03  |  blue        | +  2          |  Mary Smith  |  1962-07-25  |  red         | +  3          |  Jane Smith  |  1968-04-28  |  black       | +  4|         Joe Smith      |  1975-11-07  |  green       | +-------------+--------------+--------------+--------------+ 

What About Relationships?

Relational databases get their name from the relationships that exist between the multiple tables contained in them. You could have a series of disconnected tables in a relational database, but where's the fun in that? As you'll learn in the next section, not only is it not fun, it's also inefficient. That's where database normalization comes into play. But for now, just understand a bit about relationships.

Relationships between tables occur because they have fields in common. Usually these common fields are the unique fields, or keys. The simplest example of a relational database is a product catalog and ordering mechanism. One table has product information, and the other contains customer information. A third table has order information. Instead of creating one large table for orders, containing all product and customer information, simply use unique identifiers in the orders table to reference products in the product table and customers in the customer table.

For example, records in the MY_PRODUCTS table will have a unique PRODUCT_ID, a TITLE, a DESCRIPTION, and a PRICE:

 +---------------+-----------+----------------------+---------------+ +  PRODUCT_ID   |  TITLE    |  DESCRIPTION         |  PRICE        | +---------------+-----------+----------------------+---------------+ +  12557        |  Hat      |  Warm and fuzzy      |  7.50         | +  12558        |  Jacket   |  Waterproof          |  32.50        | +  12559        |  Shirt    |  Shiny and stylish   |  48.00        | +  12560        |  Pants    |  Pleated chinos      |  52.00        | +  12561        |  Socks    |  Wool!               |  14.99        | +---------------+-----------+----------------------+---------------+ 

Next, records in the MY_CUSTOMERS table will have a unique CUSTOMER_ID, a NAME, and an ADDRESS:

 +----------------+---------------+-------------------------------------+ +  CUSTOMER_ID   |  NAME         |  ADDRESS                            | +----------------+---------------+-------------------------------------+ +  125           |  Mike Jones   |  112 Main St, Anywhere CA 95228     | +  268           |  Jim Smith    |  458 Bee Ave, Blankville IN 55248   | +  275           |  Nancy Jones  |  751 14th St NW, Notown MI 44322    | +----------------+---------------+-------------------------------------+ 

Records in the MY_ORDERS table will have a unique ORDER_ID. You can use the CUSTOMER_ID and PRODUCT_ID identifiers to fill in the CUSTOMER and PRODUCT_ORDERED fields. Finally, if you're ordering something, you have to have a QUANTITY:

 +------------+-------------+--------------------+------------+ +  ORDER_ID  |  CUSTOMER   |  PRODUCT_ORDERED   |  QUANTITY  | +------------+-------------+--------------------+------------+ +  1         |  125        |  12558             |  1         | +  2         |  268        |  12560             |  1         | +  3         |  275        |  12557             |  1         | +------------+-------------+--------------------+------------+ 

To create an invoice for these orders, use the values in the CUSTOMER and PRODUCT_ORDERED fields to find the extended information about these people and items in their respective tables: MY_CUSTOMERS and MY_PRODUCTS.

Using the relationships between your tables, you could create an invoice like this:

 ORDER NUMBER:      001 SHIP TO: Mike      Jones, 112 Main St, Anywhere CA 95228 ITEMS ORDERED:     1 Hat (Warm and Fuzzy) TOTAL COST: $      7.50 

With this basic understanding of unique fields and the relationships between tables in a database, it's time to delve further into what is called database normalization.



PHP Essentials
PHP Essentials, 2nd Edition
ISBN: 1931841349
EAN: 2147483647
Year: 2002
Pages: 74

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net