Show
Relationships are meaningful associations between tables that contain related information — they’re what make databases useful. Without some connection between tables in a database, you may as well be working with disparate spreadsheet files rather than a database system. As we covered in our short overview of databases, databases are collections of tables, and those tables have fields (also known as columns). Every table contains a field known as an entity (or primary) key, which identifies the rows within that table. By telling your database that the key values in one table correspond to key values in another, you create a relationship between those tables; these relationships make it possible to run powerful queries across different tables in your database. When one table’s entity key gets linked to a second table, it’s known as a foreign key in that second table. Identifying the connections you’ll need between tables is part of the data modeling and schema design process — that is, the process of figuring out how your data fits together, and how exactly you should configure your tables and their fields. This process often involves creating a visual representation of tables and their relationships, known an entity relationship diagram (ERD), with different notations specifying the kinds of relationships. Those relationships between your tables can be:
Giving some thought to how your tables should relate to each other also helps ensure data integrity, data accuracy, and keeps redundant data to a minimum. One-to-one relationshipIn a one-to-one relationship, a record in one table can correspond to only one record in another table (or in some cases, no records). One-to-one relationships aren’t the most common, since in many cases you can store corresponding information in the same table. Whether you split up that information into multiple tables depends on your overall data model and design methodology; if you’re keeping tables as narrowly-focused as possible (like in a normalized database), then you may find one-to-one relationships useful. Example one-to-one relationshipLet’s say you’re organizing employee information at your company, and you also want to keep track of each employee’s computer. Since each employee only gets one computer and those computers are not shared between
employees, you could add fields to your In this case, the entity key from our The exact formatting of the lines used to connect tables in an ERD (known as crow’s foot notation) varies; sometimes you’ll see plain lines indicating a one-to-one relationship, other times those lines will have crosshatches like in figure 1. One-to-one relationships can be also useful for security purposes, like if you want to store sensitive customer information in a separate table, which you link to your main One-to-many relationshipOne-to-many relationships are the most common type of relationships between tables in a database. In a one-to-many (sometimes called many-to-one) relationship, a record in one table corresponds to zero, one, or many records in another table. Example one-to-many relationshipFor example, think about tables for customers and their orders, like in Metabase’s Sample Database, where one record from the While the one person can be linked to many orders, the reverse is not true — orders are only linked to a single record in the Many-to-many relationshipA many-to-many relationship indicates that multiple records in a table are linked to multiple records in another table. Those records may only be associated with a single record (or none at all) but the key is that they can and often are linked to more than one. Many-to-many relationships aren’t very common in practical database use cases, since adhering to normalization often involves breaking up many-to-many relationships into separate, more focused tables. In fact, your database system may not even allow for the creation of a direct many-to-many relationship, but you can get around this by creating a third table, known as a join table, and create one-to-many relationships between it and your two starting tables. In this sense, the Technically speaking the Further reading:
Thanks for your feedback! Get articles like this one in your inbox every month How does joining tables help ensure data integrity?Joining tables helps ensure data integrity by allowing for the creation of relationships between data. This allows users to see how data from different sources is related, and also allows for the enforcement of data integrity rules.
What are relationships in database?Database relationships are associations between tables that are created using join statements to retrieve data.
Which of the following relationships Cannot be implemented in a relational database?Relational database systems usually don't allow you to implement a direct many-to-many relationship between two tables.
How are relationships between tables expressed in a relational database?Relationships between tables are expressed by identical data values stored in the associated columns of related tables in a relational database. Data independence refers to the separation of data descriptions from the application programs that use the data.
|