In general, a relationship describes any objects, people, or ideas that are connected in some form.
In a database, a relationship or relation refers to two or more tables that share or link information. In other words, an account stored in a relational database may have tables with data that is connected to other tables. A database has three kinds of relationships: one-to-one (1:1), one-to-many (1:∞), and many-to-many (∞:∞). Related records can be specified as mandatory or optional, and are implemented using primary keys and foreign keys. In a NoSQL database, tables and keys may be non-existent or hidden, although the relationships can still be implemented.
Relationship explanations
- One-to-One - Each record in a table has zero (unless mandatory) or one related record in the linked table. This concept is useful for splitting tables vertically when the necessary record length exceeds the maximum record length. It’s also useful when performance can be improved by moving long, variable-length fields to another table.
- One-to-Many - Each record in the one-side table has zero (unless mandatory) or one record for zero (unless mandatory, then one) or more records in the many-side table. This type is the most common relationship. An example is one car dealership with many cars, where each car belongs to one and only one dealership.
- Many-to-Many - Records in both tables can have multiple linked records in the other table. An example is a teacher can have many students and a student can have many teachers. Usually, this concept is implemented using two one-to-many relationships to an intermediary table.
tblTeacher -> tblTeacherStudent <- tblStudent
In this example, tblTeacher stores the teachers, tblStudent stores the students, and tblTeacherStudent stores all the linked records.
Business terms, Customer Relationship Management, Device relationship management, Many-to-many data relationship, Relational database
