School Map:
Home / Enroll
$ Student Records; Class
$ News @ LIVE
Tech Radio * Support/FAQ
| Store | FORUMS
Review and Preview In last Week's Class we began our discussion of Databases, and created a simple Table. As we saw last week, Tables can be related to each other with Record identification Fields - Keys. We studied a diagram of a 'One-To-Many' relationship. This Week we will get in more deeply and put to use some of the lessons from last week, and introduce and apply some more advanced concepts. We will create the Tables of our database that will hold data for a browser based application written in the server side scripting language PHP. The application will enable You to keep track of the companies and organizations that are advertising on or sponsoring Your web sites. This will involve correlating many sites You may be operating with many sponsor advertisers - a 'Many-To-Many' relationship, which we will discuss in detail. We will see how the information queries are written in the weeks to come, this week we are designing our schema and creating the Tables for our database structure.
Schemas and Normalization Database structures or SCHEMAS can involve many Tables related to each other in various ways. The main idea to keep in mind when designing a schema for a database is to try to have each unit of data appear only once in the database. Not only does this avoid redundancy, but it also helps to avoid problems stemming from inconsistencies in the database. Suppose, for example, that a unit of data did appear in two or more places in Your database - and they were not the same! How would You know which one was correct? Another important aspect to consider are excessive NULL or 'empty' values, which can cause problems with calculations and will slow down data recovery. Entering data in the smallest 'atomic' units possible, entering data into the database structure once, and avoiding excessively redundant NULL values, is the basis of what is known as NORMALISATION. Achieving normalization often involves moving Records into separate Tables to avoid data and NULL value redundancy. If You know a Field will be left NULL most of the time, it is better to place it on a separate Table and only add Records to that Table as required. Normalization makes Your database more flexible and efficient for information storage and retrieval. OPTIMAZITION is generally applied to writing queries - Your data handling commands to the database.
Designing Your Schema Begin by dividing and grouping Your project into entities (Tables) and the attributes (Fields) which are always directly associated with them - for example the Table Sponsors and the Field Sponsor Name, Sponsor URL, etc.. How You group Your Fields in a Table determines the content of each Record. Fields which contain redundant data should be removed to separate Table(s) - If a Field will hold the same data in multiple Records in a Table, move the Field to another Table where the data will only need to be entered once, and create a relationship with a Key Field. Break down Fields into their smallest logical units and make each a separate Field - for example First and Last names... At some point in the future You may wish to search by Last name only. Breaking down data into it's smallest parts makes Your design flexible for future demands. Although You can always add new Fields and Tables to a database structure, splitting 'mixed' Fields can be a nightmare. Take the time to consider, anticipate and include all the data Fields You'll need during the design phase - sometimes adding a new Field creates redundancies or necessitates changing the relationships between Tables - perhaps requiring a major redesign of both scripts and database and doing a data transfer.
The way a database works depends on the relationships between the entities You want to represent as Tables. The three basic relationships in a database are:
The standard way of drawing a schema for planning a database is to represent entities (Tables) with rectangles, and the relationships are shown as lines which connect the Tables (sometimes relationship labels are shown in diamonds). Attributes (Fields) are within the Tables, or are shown as rectangles with rounded corners joined to their Tables with lines. By creating a visual representation this way, it is much easier to design what can be a very complex structure. Be sure that the Key Fields (which enable the relationships), that You'll need to join the data You'll want to display or use, are on both of the Tables in each direct relationship! (see week #1 for a simple explanation of using Key Fields to create relationships) Consider the relationships between the Tables, and label them. Determine where the Many-To-Many relationships are... You'll need to add a 'correlation' Table for this type of relationship, as we'll discuss in detail below. Consider the types of data and use the most efficient, but adequate, Field for each one.
The schema diagram below represents our basic database for
this project. It uses the Field names for easier reference later.
We are using 'w' for Websites, 's' for Sponsors and 'ws' for Website_Sponsor.
Abbreviating Field names minimizes typographic errors. Descriptions of
these Fields will be shown in other graphics, below. 'Many' is represented
as the mathematical symbol for infinity: 
,
but You may see this as 'N' and/or 'M' in other texts.
Many-To-Many Relationships Since the program we will be creating in this course involves relating what may be many possible sponsors to many possible websites - we will need a Many-To-Many relationship. Websites may have many different sponsors, and Sponsors may appear on many different Websites. We might wish to know which Websites have a particular Sponsor, or which Sponsors are on one of our Websites. We could repeat the process to display all of the Sponsors for each of the Websites, and all of the Websites for each of the Sponsors - and produce a list with perhaps many repeating entries - certainly not the way we'd want to store the basic data. For efficiency, this type of database relationship involves three Tables. The first will hold our Websites information, the last will hold our Sponsors information, and the Middle Table will enable us to relate the other two to each other. It is done this way since any two Tables can only directly relate to each other in One-To-One or One-To-Many relationships. This is because we can only search for one data condition at a time in each Table.
You may be thinking, "Why don't we just list all of the Sponsors for each Website in each Website Record, and list all of the Websites where each Sponsor appears in the Sponsor Records?". Consider the error prone and inefficient redundancy, the difficulty of storing and retrieving such information within a single Field or an unknown number of Fields, the manipulations involved in parsing and using the data, and also the problems encountered during changes - insertions, updates and deletes. The three Table method is actually easier, simpler, and faster. In a relational database system, relationships are Your friends!
Relationship-Chart As You can see in the diagram above, to get information on the Sponsors for a Website, You would look up all of the occurrences of the Website ID in the Middle Table and find the Sponsor ID's, then use those Sponsor ID's to get the details from the Sponsor Table. This is shown with red circles and lines. You can see that MySite2 has SuperSite, ShoutAd and CashCow as Sponsors. If You wanted to know which Websites were using a given Sponsor, You would find all of the Records where there are occurrences of the Sponsor's ID or s_id as ws_s_id on the Website_Sponsor Table, and from each of those Records get the ws_w_id to relate to the w_id or Website ID to pull data off the Websites Table, as shown in blue. You can see that CashCow is also used on MySite3. You can also see that the same relationship Keys are used to find Sponsors on Websites and Websites from Sponsors - we always go from one (at a time) on one side and find as many on the other side as there are. Notice that both of the One-To-Many relationships have the 'Many' side in the middle Correlation Table.
We add new Websites or Sponsors by adding Records to their Tables, and then relate them to each other with the Website_Sponsor Table. When we want to relate Websites to Sponsors (and Sponsors to Websites), all we have to do is add a Record to the Website_Sponsor Table. For example, if we wanted to add CashCow to MySite4 we would add a new Record to the Website_Sponsor Table as ws_w_id = 4 and ws_s_id = 4. Since we will be using them frequently, we can justify having both Fields in the Website_Sponsor Table as Index Keys, for faster data retrieval. See SQL to Create Website_Sponsor Table for how this is done. We will not make either one a Primary Key Field which requires unique values for every Record. Also notice that the Website_Sponsor Table does not need an automatically incrementing ID Key Field for every Record. (introduced in Week #1 and discussed in detail below)
Website_Sponsor Table will have the following Fields:
| Field Number |
Field Name |
Field Description |
Field Type | Null | Default | Attributes | Index Key |
Auto Increment |
|---|---|---|---|---|---|---|---|---|
| 1 | ws_s_id | Sponsor ID | MEDIUMINT(8) | No | 0 | UNSIGNED | INDEX ws_s_id |
No |
| 2 | ws_w_id | Website ID | MEDIUMINT(8) | No | 0 | UNSIGNED | INDEX ws_w_id |
No |
SQL to Create Website_Sponsor Table
<?php // Refer to week #1 to create a database before implementing this code. //
Connect to Your MySQL server (use YOUR parameters): //
Connect to database: //
Create php variable containing Table Creation SQL Query: //
Execute Query: ?> Save plain
text script file (from <?php
to ?>
inclusive) on Your server as: make_table_Website_Sponsor.php Note: You can also combine all of the Table creation scripts in this weeks class into a single script. However, keeping them separate will allow You to re-create individual tables as necessary. We'll discuss saving data and recreating Tables in the last week of this course. For
the full CREATE TABLE syntax, see the MySQL manual: |
Sponsors Table will have the following Fields:
| Field Number | Field Name |
Field Description | Field Type | Null | Default | Attributes | Index Key |
Auto Increment |
|---|---|---|---|---|---|---|---|---|
| 1 | s_id | Auto Increment ID | MEDIUMINT(8) | No | UNSIGNED | PRIMARY s_id |
Yes | |
| 2 | s_name | Sponsor Name | VARCHAR(255) | No | ||||
| 3 | s_url | Sponsor URL | VARCHAR(255) | No | ||||
| 4 | s_staturl | Statistics URL | VARCHAR(255) | Yes | NULL | |||
| 5 | s_email | Sponsor Email | VARCHAR(255) | Yes | NULL | |||
| 6 | s_uname | Username | VARCHAR(255) | Yes | NULL | |||
| 7 | s_pass | Password | VARCHAR(255) | Yes | NULL | |||
| 8 | s_date | Signup Date | DATE | Yes | NULL | |||
| 9 | s_status | Status | ENUM ('Pending', 'Active', 'Inactive', 'Defunct') |
No | Pending |
VARCHAR and CHAR We see a new Field type: VARCHAR, which is a way to store up to 255 characters. You declare VARCHAR(N) where N is any number from 1 to 255, allowing up to N number of characters. If the data entered doesn't use all of the assigned space, MySQL will only use the amount of space required to store the data plus one byte for it's length. There is also a CHAR(N) Field, (N >= 1 && N <= 255), which right pads shorter strings with spaces to N, and removes the spaces when retrieving the data. When storing large amounts of data, using VARCHAR can save a significant amount of room on the servers harddrive and speed recovery of data, although there may be some sacrifice in speed compared to CHAR when inserting data. In situations where there are very frequent updates, CHAR may be a better choice.
NULL You'll also notice we are using the NULL parameter to avoid triggering errors, which occur if a Field that requires data is not provided with it during an insert operation. We will construct our Table so that data is required in the s_id, s_name, s_url and s_status Fields, while in all the others data is optional and may be NULL (non existent). NULL means that no data is required in that Field. A Field that is designated as NULL will have NULL inserted into the Record by default if no value is provided during an insert operation.
ENUM & SET Another new Field type is ENUM, which allows You to create a list of possible values for a Field. There can be up to 65535 values in an ENUM list. The ENUM database Field can only contain one of the possible data values that are on the list. We are using it for our Sponsor Status Field. Since we are not designating it as NULL, this Field must always contain a value. If no value is provided, the default value will be 'Pending'. A related Field type is SET which allows You to have more than one of the possible values from the list stored in the database Field. A SET list can have up to 64 possible values.
Auto-Increment Indexes We will create s_id, an Auto_Increment Primary Index Field as an unsigned MEDIUMINT. As You'll recall from last week, Auto_Increment 'id' Fields automatically insert the largest existing 'id' plus 1 as Index Keys for new Records if You do nothing or insert NULL. You can override this automatic action by inserting any unused integer. Since unsigned MEDIUMINT Fields can only go up to the integer 16777215 this Table will only be able to contain 16,777,215 Records. If You want it to hold more than that You can use an unsigned INT for up to 4,294,967,295 Records and BIGINT, which can go to 9,223,372,036,854,775,807 can almost certainly hold the maximum number of Records in one Table MySQL and most operating systems can handle. Note: Maximum Table Size is usually limited by the operating system to about 4 GB of data, Linux-Alpha can hold up to 8 TB of data, MySQL itself can handle Tables with up to 8 million Terabytes of data or 2^63 bytes. As You can see, databases can be HUGE!
SQL to Create Sponsors Table
<?php // Refer to week #1 to create a database before implementing this code. //
Connect to Your MySQL server (use YOUR parameters): //
Connect to database: //
Create php variable containing Table Creation SQL Query: //
Execute Query: ?> Save plain text script
file (from <?php
to ?>
inclusive) on Your server as: make_table_Sponsors.php Note: You can also combine all of the Table creation scripts in this weeks class into a single script. However, keeping them separate will allow You to re-create individual tables as necessary. We'll discuss saving data and recreating Tables in the last week of this course. For
the full CREATE TABLE syntax, see the MySQL manual: |
Sponsors Table - Additional Fields You may wish to add to the Sponsors Table: Description, Profitability, Last Update, mailing address Fields, contact names or whatever else You need. You may want to add a FULLTEXT Index to optimize searching, as we'll discuss below in the Websites Table.
Websites Table will have the following Fields:
| Field Number | Field Name |
Field Description | Field Type | Null | Default | Attributes | Index Key |
Auto Increment |
|---|---|---|---|---|---|---|---|---|
| 1 | w_id | Auto Increment ID | MEDIUMINT(8) | No | UNSIGNED | PRIMARY w_id |
Yes | |
| 2 | w_name | Website Name |
VARCHAR(255) | No | FULLTEXT w_search |
|||
| 3 | w_url | Website URL |
VARCHAR(255) | No | FULLTEXT w_search |
|||
| 4 | w_desc | Website Description |
TEXT | Yes | NULL | FULLTEXT w_search |
||
| 5 | w_status | Status | ENUM ('Pending', 'Active', 'Inactive', 'Defunct') |
No | Pending |
TEXT and BLOB We're introducing new Fields: TEXT and BLOB (Binary Long Object) Field types store long strings of characters. TEXT Fields ignore case for sorting and comparison, while BLOB Fields recognize case differences (CAPITOL & 'baby' Characters). TEXT and BLOB Fields are capable of holding up to 65,535 characters. MEDIUMTEXT and MEDIUMBLOB Fields up to 16,777,215 characters. There are also TINYTEXT and TINYBLOB Fields each holding up to 255 characters, and LONGTEXT and LONGBLOB nominally up to 4,294,967,295 characters but due to typical transmission constraints of 16M per total Record they are realistically the same as the MEDIUM types. If You insert a value that is longer than the Field can store, it will be truncated - use error checking and handling to prevent this in critical situations. Communication buffers and available memory on the server may also be limitations. Unlike all other Field types, TEXT and BLOB types store data in memory in separate objects when the Table is opened - consider the amount of available memory on Your server when dealing with large amounts of information in such Fields.
FULLTEXT Index Since version 3.23.23 MySQL has the ability to search CHAR, VARCHAR, TEXT and BLOB Fields through a fast FULLTEXT Index. Since version 4.0.1 it can do 'boolian' or weighted or preferred searches where words can be required, avoided, given higher or lower preference, given wildcard suffixes, and grouped into phrases and sub expressions. Boolian searches can also be done on appropriate Fields that are not FULLTEXT Indexed, but such searches are very slow. We'll see how search queries are written in the weeks ahead. Today simply examine the way the FULLTEXT Index is created in the SQL statement to construct our Websites Table. Note that, like other Indexes, it can involve one or more Fields, and that when the Index is used the search will include all of the Fields it contains. The FULLTEXT Index is included here for instructional purposes, You may wish to leave it out when You build a version of this application for Your own personal use.
SQL to Create Websites Table
<?php // Refer to week #1 to create a database before implementing this code. //
Connect to Your MySQL server (use YOUR parameters): //
Connect to database: //
Create php variable containing Table Creation SQL Query: //
Execute Query: ?> Save plain text script
file (from <?php
to ?>
inclusive) on Your server as: make_table_Websites.php Note: You can also combine all of the Table creation scripts in this weeks class into a single script. However, keeping them separate will allow You to re-create individual tables as necessary. We'll discuss saving data and recreating Tables in the last week of this course. For
the full CREATE TABLE syntax, see the MySQL manual: |
Possible Additional Fields You may wish to add to the Websites Table Your Username(s) and Password(s), FTP information, email and mail list account information, profitability - or whatever else You may need. You may want to create another Table to hold Host Server Information linked to the Websites Table by w_id. Or You may choose to make it another Many-To-Many relationship, with a correlation Table 'in between' - if You are mirroring Your sites on multiple servers.
Viewing Database Structures MySQL has several useful, but unique proprietary commands which are not standard SQL. Many DBMS have similar commands. Among these are:
Notes: Optional command TERMS and parameters are shown in square
brackets [TERM parameter].
The LIKE term is
for searching a parameter The multiple character wild card is:
%
Example Script to show the structure of the Sponsors
Table //
Display results: Save plain text script
file (from <?php
to ?>
inclusive) on Your server as: show_table_structure.php |
Basic Assignment for Week #2: Construct the Tables as indicated in this week's lesson. Go back to Week #1 to see how to do this if You have to. Study CREATE TABLE queries in the MySQL manual if You wish.
Advanced Assignments for Week #2: While doing the Basic Assignment: Add a description Field to the Sponsors Table and add a FULLTEXT Index for it which includes the url and name Fields. Create an additional Table called 'Hosts' and relate it to the Websites table. Make it a One-To-Many relationship for many possible Websites on each Host server... Or, for an even more advanced challenge, assume that You are using multiple Hosts to mirror Your Websites, and make it a Many-To-Many relationship. Consider the path and sequence to find the Hosts of a particular Sponsor.
Need help with your assignment or have questions
about the first week's course?
|
School Map:
Home / Enroll
$ Student Records; Class
$ News @ LIVE
Tech Radio * Support/FAQ
| Store
Advertising
| Link To Us | Privacy
Copyright 2000-2002 Script School Productions / KMR
Enterprises
No part of this website may be reproduced, copied and/or distributed
in any medium
without express written permission