- A Look at Relational Databases
- Introduction to SQL
- To Quote or Not to Quote?
- Project I: Creating an Access Database File
- Setting Up an ODBC Connection
- Project II: Retrieving Data
- Query of Queries
- Dynamic Datasources
- SQL Builder
- Recap
This chapter is from the book
This chapter is from the book
This chapter is from the book
Project I: Creating an Access Database File
For several projects in this book, we will use a database file that you will create in Microsoft Access for the Shelley Biotechnologies web site. This is a database containing the three tables we discussed earlier: Customers, Products, and Categories.
Microsoft Access is great for setting up databases quickly or for smaller database driven web sites, but you might want to consider using something like Microsoft SQL Server or Oracle to handle larger scale web sites. These types of databases are designed to handle a large number of simultaneous requests, whereas Access is not, and response times will suffer as a result.
Use the following information to set up your tables:
Customers Table
|
Field Name |
Datatype |
|
customerID |
AutoNumber—Primary Key |
|
firstName |
Text |
|
lastName |
Text |
|
address |
Text |
|
city |
Text |
|
state |
Text |
|
zip |
Text |
|
country |
Text |
|
phone |
Text |
|
|
Text |
|
mailingList |
Text |
Products Table
|
Field Name |
Datatype |
|
sku |
AutoNumber—Primary Key |
|
name |
Text |
|
price |
Currency |
|
description |
Text |
|
weight |
Number |
|
image |
Text |
|
category |
Number |
Category Table
|
Field Name |
Data Type |
|
categoryID |
AutoNumber—Primary Key |
|
categoryName |
Text |
Below is the content we will be using throughout the examples in the following chapters. Some fields were left blank intentionally.
The database file (shelleyCatalog.mdb) is also available from our web site under Chapter 1.
)
FIGURE 1–3 Customer table.
)
FIGURE 1–4 Products table.
)
FIGURE 1–5 Category table.
Obviously, this is a brief overview of what can be done with databases. We suggest that you pick up a book specifically on databases if you plan on doing more sophisticated database design.