CCNC/CCNC Module 5/Designing and creating tables/Table Design and Layout/Schemas
 |
Table Design and Layout |
Field Design and Properties | Adding Columns | Adding Indexes | Database Schemas | Self Assessment | Summary & FAQs |
Contents
Preparing to create table relationships
Upon completion of this tutorial the learner will be able to:
|
One of the early steps in creating a database is in designing as many of the tables as can be identified through analysis. Once the databases table designs have begun to form the collection of tables and their relationships becomes known as the database schema. Designing, adding and modifying a database schema becomes a task that occurs whenever the business information requirements change. This change can happen either frequently or infrequently, this depends on how much the information requirements change. In our small school example once the database has been designed we would expect very little change to the database schema.
Adding the Addresses table
In preparation for the next tutorial, two new tables need to be developed; the Addresses and _xref_ (cross references) tables. The Addresses table will store the Address information where the _xref_ table will store the information for the many-to-many relationship. When developing a database using a relational design approach the ability to have the many-to-many relationship is done using three tables. Two tables for each end of the many-to-many and one table to manage the joining of the two end tables. Implementing a many-to-many relationship is essentially the same as implementing two one-to-many relationships. In our example there will be three tables; People, Addresses and People_xref_Addresses. The People and Addresses tables are the two ends of the many-to-many relationship and the People_xref_Addresses table manages the joining of these two tables.
Our first step in building this relationship is to create the Addresses table. This table is like many other tables, it has a primary key field and all the attributes related to the entity. Using the table designer create a table with attributes as shown and name the new table 'Addresses' when prompted to save the table.
- It is recommended that tables are named in the plural. This is due to tables storing more than one record. In our Addresses table many addresses will be stored if we were to have called the table Address it infers only one address. Having table names in the plural will help understandability.
Adding the Cross Reference table
The most important part of a cross reference table is that it contains two fields (attributes) that are the same data type(s) as the primary keys of the two tables from each end of the many-to-many relationship. In our example we will be creating a many-to-many relationship between People and Addresses. This is because people can have more than one address (home, work, etc.) and any one address can have more than one person. The cross reference table needs to have both person_id and address_id fields. When creating the cross reference table it is good design to include a xref_id to uniquely identify the cross reference record and to include a description field that describes the type of reference (home, work, etc.). In this example is the address a home address, a work address, a post office box, etc.
When saving the cross reference table it is a good idea to name the table with the table names of the two ends of the many-to-many with _xref_ between the two table names. In our example the joining of People with Addresses becomes People_xref_Addresses. This approach to naming helps understandability.
Test your knowledge
|
|
