- 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
- 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 of the projects throughout the rest of the book we will be using a database file that you will create in Microsoft Access for the Shelley Biotechnologies website. This is a database containing the three tables we discussed earlier: Customer, Products, and Categories.
While Microsoft Access is great for setting up databases quickly or for smaller database-driven websites, you might want to consider using something like Microsoft SQL server or Oracle to handle the larger-scale websites. These types of databases are designed to handle a large number of simultaneous requests whereas Microsoft Access is not and response times will suffer as a result.
Use the following information to set up your tables:
|
Customer Table |
|
|
Field Name |
Data Type |
|
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 |
Data Type |
|
sku |
AutoNumber—Primary Key |
|
name |
Text |
|
price |
Currency |
|
description |
Text |
|
weight |
Text |
|
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 website under chapter 1.
FIGURE 1–3 Customer table.
){kind=link}
FIGURE 1–4 Products table.
){kind=link}
FIGURE 1–5 Category table.
){kind=link}
Obviously this was just a brief overview of what can be done with databases. We suggest you pick up a book specifically on databases if you plan on doing more sophisticated database designs.