ASA Getting Started
Designing and Building Your Database
Tutorial: Design and build a simple database
In this lesson, you learn about designing and creating relationships between tables, using foreign keys.
For more information, see Tables are related by foreign keys.
Although each table contains information about a single subject, two or more tables may contain related information. For example, an employee is a member of a department, or a sales order is for a set of products. Relationships in a database may appear as foreign key relationships between tables, or may appear as separate tables themselves. You will see examples of each in this chapter.
You create relationships in your database to encode rules or practices that govern the data in your tables. Once a relationship is built into the structure of the database, there is no provision for exceptions.
Relationships among tables are classified as follows.
One-to-one relationships Each item in one entity corresponds to either zero or one entity in another. For example, in the sample database, one employee manages one department. There is nowhere to put a second department manager. Duplicating the department entry would involve duplicating the department ID, which is not possible because it is the primary key.
It is often appropriate to combine the items in a one-to-one relationship into a single table. There is a column in the department table for a manager, rather than having a separate table named manager.
For cases where it is appropriate to keep the items separate, see Designing Your Database.
Many-to-one relationships A many-to-one relationship becomes a foreign key relationship between tables. In a many-to-one relationship, the primary key in the one entity appears as a new foreign key column in the many table.
For example, in the database you just created, one customer can place many orders, but only one customer places each order. To represent the one-to-many relationship, you need a foreign key column in the sales_order table (cust_id) that maps to the primary key column in the customer table (id). It is often convenient to give the two columns the same name.
Each entry in the cust_id column of the sales_order table must match one of the entries in the id column of the customer table. The sales_order table (which contains the foreign key in the relationship) is called the foreign table or referencing table. The customer table (which contains the referenced primary key) is called the primary table or the referenced table.
Many to many relationships A many-to-many relationship is represented by an intermediate table, and there is a foreign key relationship from the intermediate table to each of the related entities.
For example, in the sample database, there is a many-to-many relationship between products and sales orders. One sales order can be for many products, and one product can appear on many sales orders.
In some cases, the intermediate table (sales_order_items) contains additional information, such as the number of items of the product that were ordered and the date they were shipped. In this case, the intermediate table holds no additional information.
Add foreign keys to relate the tables in your database.
Add the following foreign keys:
A foreign key from the id column in sales_order_items, referencing the id column in sales_order. This key builds the many-to-one relationship between sales orders and sales order items into the database.
A foreign key from the prod_id column in sales_order_items, referencing the id column in product. This key builds the many-to-one relationship between sales order items and products into the database.
A foreign key from the cust_id column in sales_order, referencing the id column in customer. This key builds the many-to-one relationship between sales orders and customers into the database.
The first two foreign keys taken together build the many-to-many relationship between sales orders and products into the database.
To create a foreign key
Select the table for which you wish to create a foreign key.
Click the Foreign Keys tab in the right pane.
From the File menu, choose New
Follow the instructions in the wizard.
This completes this introductory section on designing and building relational databases. Remaining chapters in the book describe how to add and retrieve data from databases. These chapters use the Adaptive Server Anywhere sample database, which is a bigger database than the one you have just created.