Share this Page URL

Chapter 2. The Basics of Database Design > Planning and Designing the HelloWorl... - Pg. 47

The Basics of Database Design Requirement 47 Design Implementation (as you'll learn more about later in this chapter). We actually don't need to store this data at all. We know what country our customers are in (Country is a field in the Northwind Customers table), and we know what products those customers order. We can retrieve the data we need through table relationships in a query, as you'll learn more about in Chapter 6 and 9. This requirement is related to the second requirement, that we need to track campaigns in several countries. We'll create a table to store information about marketing materials in different lan- guages. We'll use the Hyperlink data type to store links to the files that contain the materials. Campaigns and expenses have a one-to-many relationship: one campaign can have many expenses, but each expense (or portion of an expense) is related to one campaign. Campaign expenses should be stored in a separate table that's related to the marketing campaigns table. We'll include the field for the overall campaign budget in the marketing campaigns table. We'll address the need for the finance department to refer to this data in Chapter 4, when we explain how to import data. The relationship between campaigns and tasks is a one-to-many relationship. We will track tasks in a set of separate tables. The main tasks table will have a relationship to the marketing cam- paigns table and the Employees table in Northwind. We need fields to track current status, when the task is due, priority of the task, and employee comments. Link to marketing presentations and other marketing materials that will be used for advertising and sales meetings. The presen- tations and sales materials are stored in separate files. Track which language is being used for the campaign in each country. Track budget and expense information for each marketing cam- paign. Expenses will be tracked in several predefined categories. The finance department needs to reference the campaign expense data frequently. The database also needs to update expense data frequently. Manage the workflow involved in coordinating each campaign. Be able to define and assign tasks to employees and to a cam- paign, track the status of each task, rank the priority of a task, and provide a means for the employee assigned to a task to comment on its status. Each task will be assigned to a single campaign, and each campaign will have more than one task associated with it. In the next section, we'll make some modifications to the Greetings table we created in Chapter 1 and then create the tables for tracking marketing materials and associating a campaign country with a language. Later in this chapter, we'll use the Table Wizard to create the initial campaign expenses