How to define a relationship between database tables

When designing a relational database, the most important part of the work is how to assign data elements to each relational datasheet. Once the classification of these data elements has been completed, the operation of the data will depend on the relationship between the data tables, through which the relationships between the data tables can be linked in a meaningful way. For example, if you do not know which user placed the order, then the individual order information is of no use. However, you do not need to store both customer and order information in the same datasheet. You can store customer information and order information separately in two relational data tables, and then use the relationships between the two data tables to view each order in the datasheet and its associated customer information at the same time. If a normalized datasheet is the basis of a relational database, then the relationship between the tables is the cornerstone of these foundations.

Starting point

The following data will be used in the example in this article to illustrate how to define the relationships between database tables. After normalization of the data by Boyce-codd Normal Form (BCNF), seven relational tables are produced:

Books: {Title*, ISBN, Price}
Authors: {FirstName*, LastName*}
ZIPCodes: {ZIPCode*}
Categories: {Category*, Description}
Publishers: {Publisher*}
States: {State*}
Cities: {City*}

What you need to do now is to show how to build relationships between these tables.

Relationship Type

At home, you have a lot of relationships with other members. For example, you have a relationship with your mother, you have only one mother, but your mother may have several children. You are related to your siblings-you may have a lot of brothers and sisters, and they also have many brothers and sisters. If you're married, you and your spouse have a spouse--it's mutual--but only one at a time. At the level of the datasheet, the relationship between the database and the phenomena described above is very similar. There are three different types of relationships:

One-to-one: In this relationship, there can be only one record on each side of the relational table. Keywords in each datasheet can have only one record in the corresponding relational table or no corresponding record. This relationship is very similar to a pair of spouses-either you are married, you and your spouse can only have one spouse, or you are not married or have a spouse. Most one-to-one relationships are the result of some kind of business rule constraint, not the natural attributes of the data. Without the constraints of these rules, you can usually combine two data tables into a single datasheet without breaking any of the normalized rules.

One-to-many: A primary key data table can contain only one record, and in its relational table this record can be related to one or more records, or no records associated with it. This relationship is similar to the relationship between you and your parents. You have only one mother, but your mother can have a few children.

Many-to-many: Each record in two data table can be related to any number of records (or no records) in another datasheet. For example, if you have more than one sibling, which is the same for your siblings (with multiple siblings), the Many-to-many relationship requires the introduction of a third data table called a Contact table or connection table, because a relational system cannot directly implement this relationship.

Establish a relationship

Before you start thinking about relationships between relational tables, you might want to be familiar with the data. Only when you are familiar with the data will the association be more obvious than when you first started. Your database system relies on matching values found in two data tables to establish a relationship. If a matching value is found in the database system, the system extracts the data from both data tables and creates a virtual record. For example, you might want to see all the books written by a particular author, and in this article, the system will look for related matching values from the two data tables, "books" and "Authors". It is important to note that in most cases the results of the query are dynamic, which means that any changes made to this virtual record will probably work on the underlying datasheet.

The values that are matched are the values of the primary key and the foreign key. (The relational model does not require a relationship to be determined by using a primary key.) You can use any of the alternate keywords in a datasheet to establish a relationship, but using a primary key is a standard that everyone has accepted. The primary key (primary key) is a unique identification table for each record. The outer key (foreign key) simply stores the primary key in a datasheet in another datasheet. Similarly, you don't have to do much work-simply add the primary key to the relational table and define it as a foreign key.

The only thing to note is that the data type of the foreign key field must be the same as the data type of the primary key. However, some systems can allow an exception to this rule, which allows for the establishment of relationships between numeric and AutoNumber (autonumbering) fields, such as access to identity and AutoNumber in SQL Server systems. In addition, the value of the foreign key can be null (NULL), although it is strongly recommended that you do not leave the foreign key blank for no particular reason. You may never have a chance to use a database that requires this feature.

Now go back to our example relational table and start typing the appropriate foreign key. (Please continue to draft on paper-it is too early to create a real datasheet in your database system.) It's much easier to correct errors on paper. Remember that you are adding the value of the primary key to the relational table. Just call the relationship between the entities, and the others are simple:

Books and classifications are related.

Books and publishers are related.

Books are related to the author.

The author and postcode (Zip) are related.

The ZIP code and the city are related.

The city and the state are related.

This step is not static, and you may find it easier to add a foreign key to the normalization process. When you move a field to a new datasheet, you may want to add the primary key of the new datasheet to the original datasheet as a foreign key. However, foreign keys often change as you continue to normalize the remaining data. You'll find it more efficient to add all the foreign keys one at a time after all of these tables have been normalized.