Database Associations Overview
Database Essentials - Part 4
Division 1
Forward: In this part of the series, we look at Database Associations Overview.
By: Chrysanthus Date Published: 4 Aug 2012
Introduction
Example
In this example there are two tables (entities). There is a business rule governing the two tables. Open the following link in a new browser window tab to see the two tables (link below).
Imagine that you are a whole seller selling to customers (supermarkets and stores) and you have a database in a computer for your business transactions. Table 4.1 shows a bit of a Sale table for you. Table 4.2 shows the Sale items. Table 4.1 deals with the sales you made to customers and Table 4.2 deals with the products for each sale. In Table 4.1, the primary key is SaleID. In Table 4.2 you have a composite key for the primary key, made up of SaleID and ProductID. Remember, each primary key value uniquely identifies a row of its table.
In Table 4.1, the primary key column has numbers. The customer column has the names of the customer companies (supermarket and stores). The employee column shows the name of the employee who made the sale and typed it into the database. This column has both the first and last name of the employee, separated by commas. Do not worry too much about that (comma) for now. The last column shows the date the sale was made. That is it for the Sale table; now for the Sale Item table.
The first column in the Sale Item table is the SaleID column. The second is the ProductID column. The third is the Unit Price column; that is unit price for each product. The product is indicated by the ProductID in the row. The fourth column is quantity of the product that was sold. The fifth column indicates if any discount was made for a product sold. There is another table in the database, which you can call the Product Table. This Product Table lists the products and their descriptions and maybe some other products information like quantity available in your wholesale store. I will not talk about that table in this part of the tutorial. The Products table uses the ProductID as key to identify the products. That is it for the Sale Item table.
The two tables show Sales that the customers requested from you and you delivered. Now, you may wonder why the two tables are not combined into one. I will explain this later in the series. For now our interest is to get an overview of associations also known as relationships, in database.
Before we continue, note that in the Sale table each SaleID corresponds to one customer.
One of your business rules, which is an obvious rule, is that you can sell more than one products to the same customer. And so from the two tables, we see that for the SaleID 10, the customer bought the products with ProductIDs 11, 42 and 72. We see that for the SaleID 20, the customer bought products with ProductIDs 14 and 51. We also see that for the SaleID 30, the customer bought products with ProductIDs 41, 51 and 65.
So for one sale, there are many products. This is called a one-to-many relationship or one-to-many association, determined by the business rule.
Class Diagram
A class diagram shows the relationship between two classes (tables). Fig 4.1 on the browser tab window shows the class diagram for the above two tables. Fig 4.2 shows the detail class diagram. The detail class diagram has the attributes (columns) of the tables. A line between the two blocks shows the association (relationship). At one end of the line you have the number 1. At the other end of the line, you have the asterisk, *. The asterisk, * means many. So the 1 and the asterisk (*) mean one-to-many. One row in the Sale table corresponds to many rows in the sale Item table. The asterisk is on the side for the table that offers many rows in the relationship. The 1 is on the side for the table that offers one row in the relationship.
In the detail class diagram, the line points to the corresponding IDs.
That is an overview of what association or relationship means in database. In the next parts of the series, we shall look at the different types of associations that can exist in an organization (company). In this division, I do not show you how to determine relationships, I only show you the different types of relationships that exist.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT