on April 30, 2003, 12:00 AM PDT Database normalization is the cornerstone of database theory. Once a database is normalized, relationships between the data in multiple tables must be established. A hefty part of designing a relational database is dividing the data elements into related tables. Once you’re ready to start working with the data, you rely on relationships between the tables to pull the data together in meaningful ways. For
instance, order information is useless unless you know which customer placed a particular order. By now, you probably realize that you don’t store customer and order information in the same table. Instead, you store order and customer data in two related tables and then use a relationship between the two tables to view each order and its corresponding customer information at the same time. If normalized tables are a relational database’s foundation, then relationships are the cornerstone. Relational database design series You’re in the middle of the Builder.com relational database design series. The previous installments in this series are: Starting point The following data is used for demonstration in this article. The process of normalizing the data through Boyce-Codd Normal Form (BCNF) produced seven related tables: Books: {Title*, ISBN, Price} Authors: {FirstName*, LastName*} ZIPCodes: {ZIPCode*} Categories: {Category*, Description} Publishers: {Publisher*} States: {State*} Cities: {City*} Now it’s time to establish just how those tables are related to one another. Relationship types
Establishing relationships Those matching values are the primary and foreign key values. (The relational model doesn’t require that a relationship be based on a primary key. You can use any candidate key in the table, but using the primary key is the accepted standard.) You learned about primary keys in Part 2—a primary key uniquely identifies each record in a table. A foreign key is, simply put, one table’s primary key in another table. As such, there’s not much for you to do—simply add the primary key field to the related table, as a foreign key. The only consideration is that a foreign key field must be of the same data type as the primary key. Some systems allow one exception to this rule and will allow a relationship between a number and an autonumbering field (such as AutoNumber in Access of Identity in SQL Server). In addition, foreign key values can be Null, although the recommendation is that you not leave a foreign key Null without a very specific reason to do so. Chances are you may never work with a database that requires this capability. Return to your sample tables and start entering foreign keys as appropriate. (Continue to work with paper lists—it’s still too early to actually create tables in your database system. It’s much easier to correct a mistake on paper.) Remember, you’re adding the primary key value to a related table. Simply recall the relationships between the entities, and the rest is easy:
This particular step isn’t written in stone, and you may find it easier to add foreign keys during the normalization process. When you move a field to a new table, you’ll probably add that new table’s primary key to the original table as a foreign key. However, often the foreign keys change as you continue to normalize the remaining data. You may find it more efficient to do them all at once after all the tables are completely normalized. Let’s work through each of the tables one at a time, beginning with the Books table, which at this point has just three fields. Specifically, add the primary keys from the Authors, Categories, and Publishers tables to Books. When you’ve finished, the Books table has seven fields: Remember, the primary key in the Authors table is a complex key based on both the first and last name fields. Therefore, you must add both fields to the Books table. Notice that the foreign key field names include a FK suffix. Adding the suffix improves readability and is self-documenting. You may find it easier to track foreign keys if you identify them this way in their name. It’s all right if the primary and foreign keys don’t have the same name. There are three relationships present: Books to Authors, Books to Categories, and Books to Publishers. What might not be as apparent to you are the problems with two of these relationships:
These two relationships represent many-to-many relationships. Earlier, we told you that tables can’t directly accommodate these relationships and require a third linking table. (The Books to Publishers relationship is a one-to-many and is fine just as it’s currently stated.) Both of the newly discovered many-to-many relationships will require a linking table that contains the primary key from each table, as a foreign key. The new linking tables
are: No changes to the Categories, Authors, or Publishers tables are necessary. However, you must remove the FirstNameFK, LastNameFK, and CategoryFK foreign keys from Books: Now, let’s move on to the Authors table, which currently has two fields. Each author relates to a ZIP code value in the ZIPCodes table. However, each ZIP code
may relate to more than one author. To accommodate this one-to-many relationship, enter the primary key from the ZIPCodes table into the Authors table as a foreign key: At this point, you’re ready to tackle the remaining address components. It’s strange to see them separated into tables, but that’s the result of properly normalizing the data
through BCNF. Each ZIP code value will have one corresponding city and state value. Each city and state value will be entered just once in its corresponding table. The ZIPCodes and Cities tables require foreign key fields to accommodate the relationships: From one to nine
As a result of the sample’s simplicity, you might be wondering just how this relational business helps. It seems that you’re still storing redundant data, just differently—in the form of foreign keys. That’s because our tables have only a few fields right now. Try to imagine a table with a dozen fields. Granted, you still have to store that table’s primary key as a foreign key value in a related table, but that probably constitutes one or two extra fields at most. Compare that to the alternative of adding all dozen entries in that table for every record.
What are the types of relationship between tables?There are three types of table relationships in Access.. A one-to-many relationship. Let's use an order tracking database that includes a Customers table and an Orders table as an example. ... . A many-to-many relationship. Now let's look at the relationship between a Products table and an Orders table. ... . A one-to-one relationship.. What are the 3 types of relationships in a database?There are 3 different types of relations in the database: one-to-one. one-to-many, and. many-to-many.
What are the four 4 types of relationships in a database?There are four relationships in the database.. (1) One to One: One entity is associated with another entity. ... . (2) One to Many: One entity is associated with many other entities. ... . (3) Many to One: Many entities are associated with only one entity. ... . (4) Many to Many: Many entities are associated with many other entities.. How many relationships can be formed between tables?There are three kinds of relationships between tables. The kind of relationship that is created depends on how the related columns are defined.
|