Understanding the OWS Database Tables

Now that you've reviewed the important database fundamentals, let's walk through the tables used in the Orange Whip Studios application (the database you'll be using throughout this book).

For your convenience, the created and populated Access MDB file is on the accompanying CD-ROM.

NOTE

Tables and table creation scripts for additional databases can be found on the book web site at http://www.forta.com/books/032122367.

The database is made up of 12 tables, all of which are related. These relationships are graphically shown in Figure 5.13.

Figure 5.13. Many database applications allow relationships to be defined and viewed graphically.

NOTE

What follows isn't a complete definition of the tables; it's a summary intended to provide a quick reference that will be of use to you when building the applications. You might want to bookmark this page for future reference.

See Appendix G, "Sample Application Data Files," for a more thorough description of the tables used.

The Films Table

The Films table contains the movies list. The primary key for this table is the FilmID column.

This table contains a single foreign key:

• The RatingID column is related to the primary key of the FilmsRatings table.

Table 5.2. The Films Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
FilmID	Numeric	Unique ID for each movie; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
MovieTitle	Text	Movie title
PitchText	Text	Movie pitch text; the tag line
AmountBudgeted	Numeric, currency	Amount budgeted for movie (may not be equal to the actual cost plus expenses)
RatingID	Numeric	ID of associated rating in the FilmRatings table
Summary	Memo or long text	Full movie summary stored in a variable-length text field (to enable longer summaries)
ImageName	Text	Filename of associated image (if there is one)
DateInTheaters	Date	Expected movie release date

The Expenses Table

The Expenses table contains the expenses associated with any movies listed in the Films table.

Table 5.3. The Expenses Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
ExpenseID	Numeric	Unique ID for each expense; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
FilmID	Numeric	ID of associated movie
ExpenseAmount	Numeric, or currency	Expense amount
Description	Text	Expense description
ExpenseDate	Date	Expense date

The primary key for this table is the ExpenseID column.

This table contains a single foreign key:

• The FilmID column is related to the primary key of the Films table.

The Directors Table

The Directors table contains the list of directors. This table is related to the Films table via the FilmsDirectors table.

Table 5.4. The Directors Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
DirectorID	Numeric	Unique ID for each director; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
FirstName	Text	Director's first name
LastName	Text	Director's last name

The primary key for this table is the DirectorID column.

This table contains no foreign keys.

The FilmsDirectors Table

The FilmsDirectors table is used to relate the Films and Directors tables (so as to associate directors with their movies).

Table 5.5. The FilmsDirectors Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
FDRecID	Numeric	Unique ID for each row; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
FilmID	Numeric	ID of associated movie
DirectorID	Numeric	ID of associated director
Salary	Numeric, or currency	Director's salary

The primary key for this table is the FDRecID column.

This table contains two foreign keys:

• The FilmID column is related to the primary key of the Films table.

• The DirectorID column is related to the primary key of the Directors table.

The Actors Table

The Actors table contains the list of actors. This table is related to the Films table via theFilmsActors table.

Table 5.6. The Actors Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
ActorID	Numeric	Unique ID for each actor; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
NameFirst	Text	Actor's first name
NameLast	Text	Actor's last name
Age	Numeric	Actor's age
NameFirstReal	Text	Actor's real first name
NameLastReal	Text	Actor's real last name
AgeReal	Numeric	Actor's real age (this one actually increases each year)
IsEgomaniac	Bit or Yes/No	Flag specifying whether actor is an egomaniac
IsTotalBabe	Bit or Yes/No	Flag specifying whether actor is a total babe
Gender	Text	Actor's gender (M or F)

The primary key for this table is the ActorID column.

This table contains no foreign keys.

The FilmsActors Table

The FilmsActors table is used to relate the Films and Actors tables (so as to associate actors with their movies).

Table 5.7. The FilmsActors Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
FARecID	Numeric	Unique ID for each row; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
FilmID	Numeric	ID of associated movie
ActorID	Numeric	ID of associated actor
IsStarringRole	Bit or Yes/No	Flag specifying whether this is a starring role
Salary	Numeric or currency	Actor's salary

The primary key for this table is the FARecID column.

This table contains two foreign keys:

• The FilmID column is related to the primary key of the Films table.

• The ActorID column is related to the primary key of the Actors table.

The FilmsRatings Table

The FilmsRatings table contains a list of film ratings used in the Films table (which is related to this table).

Table 5.8. The FilmsRatings Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
RatingID	Numeric	Unique ID for each rating; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
Rating	Text	Rating description

The primary key for this table is the RatingID column.

This table contains no foreign keys.

The UserRoles Table

The UserRoles table defines user security roles used by secures applications. This table isn't related to any of the other tables.

Table 5.9. The UserRoles Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
UserRoleID	Numeric	Unique ID of user roles; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
UserRoleName	Text	User role name (title)
UserRoleFunction	Text	User role description

The primary key for this table is the UserRoleID column.

This table contains no foreign keys.

The Contacts Table

The Contacts table contains a list of all contacts (including customers).

Table 5.10. The Contacts Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
ContactID	Numeric	Unique ID for each contact; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
FirstName	Text	Contact first name
LastName	Text	Contact last name
Address	Text	Contact address
City	Text	Contact city
State	Text	Contact state (or province)
Zip	Text	Contact ZIP code (or postal code)
Country	Text	Contact country
Email	Text	Contact email address
Phone	Text	Contact phone number
UserLogin	Text	Contact login name
UserPassword	Text	Contact login password
MailingList	Bit or Yes/No	Flag specifying whether this contact is on the mailing list
UserRoleID	Numeric	ID of associated security level

The primary key for this table is the ContactID column.

This table contains a single foreign key:

• The UserRoleID column is related to the primary key of the UserRoles table.

The Merchandise Table

The Merchandise table contains a list of merchandise for sale. Merchandise is associated with movies, so this table is related to the Films table.

Table 5.11. The Merchandise Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
MerchID	Numeric	Unique ID for each item of merchandise; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
FilmID	Numeric	ID of associated movie
MerchName	Text	Item name
MerchDescription	Text	Item description
MerchPrice	Numeric or currency	Item price
ImageNameSmall	Text	Filename of small image of item (if present)
ImageNameLarge	Text	Filename of large image of item (if present)

The primary key for this table is the MerchID column.

This table contains a single foreign key:

• The FilmID column is related to the primary key of the Films table.

The MerchandiseOrders Table

The MerchandiseOrders table contains the orders for movie merchandise. Orders are associated with contacts (the buyer), so this table is related to the Contacts table.

Table 5.12. The MerchandiseOrders Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
OrderID	Numeric	Unique ID of order (order number); can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
ContactID	Numeric	ID of associated contact
OrderDate	Date	Order date
ShipAddress	Text	Order ship to address
ShipCity	Text	Order ship to city
ShipState	Text	Order ship to state (or province)
ShipZip	Text	Order ship to ZIP code (or postal code)
ShipCountry	Text	Order ship to country
ShipDate	Date	Order ship date (when shipped)

The primary key for this table is the OrderID column.

This table contains a single foreign key:

• The ContactID column is related to the primary key of the Contacts table.

The MerchandiseOrdersItems Table

The MerchandiseOrdersItems table contains the individual items within an order. Order items are associated with an order and the merchandise being ordered, so this table is related to both theMerchandiseOrders and Merchandise tables.

Table 5.13. The MerchandiseOrdersItems Table

COLUMN	DATA TYPE	DESCRIPTION AND SIZE
OrderItemID	Numeric	Unique ID of order items; can be populated manually when rows are inserted or automatically (if defined as an Auto Number field)
OrderID	Numeric	ID of associated order
ItemID	Numeric	ID of item ordered
OrderQty	Numeric	Item quantity
ItemPrice	Numeric or currency	Per-item price

The primary key for this table is the OrderItemID column.

This table contains two foreign keys:

• The OrderID column is related to the primary key of the MerchandiseOrders table.

• The ItemID column is related to the primary key of the Merchandise table.

TIP

Many database applications, including Microsoft Access and Microsoft SQL Server, provide interfaces to map relationships graphically. If your database application supports this feature, you might want to use it and then print the output for immediate reference.