Creating Relationships Between the Tables

Team-Fly    

Macromedia® DreamWeaver® MX Unleashed
By Matthew Pizzi, Zak Ruvalcaba
Table of Contents
Chapter 26.  Database Primer


Now that you've created all the tables and columns within the database, you are ready to begin creating the relationships. Remember, relationships are what make the database efficient and keep you from duplicating data within individual tables. To begin establishing relationships between tables, follow these steps:

  1. From the Tools menu, select Relationships.

  2. When you are prompted with the Show Table dialog box, add every table by selecting the first table, holding down the Shift key, and then selecting the last table. Click Add.

  3. Choose Close.

You are now ready to establish relationships between the tables. First, establish a relationship for the customers and orders tables. Notice that the customers table contains a CustomerID Primary Key and the orders table contains a CustomerID Foreign Key. The reason is that one customer can have many orders, so a one-to-many relationship must be created. The only way that can be accomplished is through the use of a foreign key. Access does not allow direct insertion of foreign keys; they are given when establishing a relationship between a primary key in one table and a field with the same data type and value in another table.

To create the relationship, simply drag the CustomerID field from the customers table into the CustomerID field in the orders table. You will be presented with the Edit Relationships dialog box, as shown in Figure 26.17.

Figure 26.17. The Relationships Editor allows quick modification for relationships between tables.

graphics/26fig17.jpg

Notice that the two fields are displayed directly underneath their corresponding tables.

Because this is a one-to-many relationship, you need to select Enforce Referential Integrity along with Cascade Update Related Fields and Cascade Delete Related Fields. This is done for referential integrity. If a customer is deleted from the Web store database, you would want all their orders deleted as well; otherwise, the database would be cluttered with redundant and, at times, unnecessary data. Notice the Relationship Type states that the relationship is indeed a one-to-many relationship. Select OK. The table that follows describes the relationships between tables:

Tables Keys Referential Integrity
Customers/Orders CustomerID Yes
Customers/CreditCards CustomerID Yes
Customers/Transactions CustomerID No
Orders/Transactions ProductID No
Orders/Products ProductID No
Products/Inventory ProductID Yes

When all the relationships have been established, the diagram should look similar to Figure 26.18.

Figure 26.18. The Relationships window should look much like a diagram with connected lines showing relationships between tables.

graphics/26fig18.jpg

A few points to consider after you have finished creating all of the relationships are the following:

  • Remember that orders and credit cards will have a many relationship to the one customer. This is so because one customer can have many orders as well as credit cards on file. Also, referential integrity is enforced because if you delete a customer from the Web store database, you also want that customer's orders and credit cards on file to be deleted as well.

  • A one-to-one relationship exists from customers to transactions. This is the case because the transactions table serves as a simple history of all transactions. Nothing should ever be deleted from this table.

  • The products table has a one-to-one relationship with the transactions table as well as the orders table because both the orders and the transactions tables share information that the product table contains.

  • A one-to-one relationship exists between the products table and the inventory table because a product can only have one quantity associated with it. You enforce referential integrity because if you delete a product, you also want the quantity from the inventory table deleted.

NOTE

Keep in mind that one-to-many relationships have icons at the end points of the relationship lines. A small "1" will appear next to the table with the one relationship and a small infinity symbol will appear, symbolizing the table with the many relationship.



    Team-Fly    
    Top


    Macromedia Dreamweaver MX Unleashed
    Macromedia Dreamweaver MX 2004 Unleashed
    ISBN: 0672326310
    EAN: 2147483647
    Year: 2002
    Pages: 321

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net