CCNC/CCNC Module 5/Designing and creating tables/Table Design and Layout/Schemas

From WikiEducator
Jump to: navigation, search
Tutorial.png Table Design and Layout 

Field Design and Properties | Adding Columns | Adding Indexes | Database Schemas | Self Assessment | Summary & FAQs

Preparing to create table relationships

Icon objectives.jpg


Upon completion of this tutorial the learner will be able to:
  • Add the tables for a many-to-many relationship
  • Identify the design elements necessary for a many-to-many relationship
  • Create meaningful table names

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

Figure 1. The Addresses tables and relationships
Figure 2. Creating the Addresses table
Figure 3. Saving 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.

Icon present.gif
Tip: Keep the table names plural
  • 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

Figure 3. Adding the Xref table
Figure 4. Saving the Xref 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

Icon qmark.gif

Database Relationships


1. A many-to-many is implemented as two one-to-many relationships?

Absolutely Correct!
Incorrect, click here to review the topic.

2. The use of good names helps understandability?

Absolutely Correct!
Incorrect, click here to review the topic.

3. In the above example the address_type field of the People_xref_Addresses table is used to;

(a) Identify if their are many addresses for the person
Incorrect, click here to review the topic.
(b) Identify if the relationship is one-to-many or many-to-many
Incorrect, click here to review the topic.
(c) Identify the type of address (work, home, etc.)
Absolutely correct!
(d) Identify which field is the primary key for the _xref_ table
Incorrect, click here to review the topic.
(e) Identify the type of person (visitor, occupant, etc.)
Incorrect, click here to review the topic.

Your score is 0 / 0