Key Relationships: Understanding The Relationships in SQL Server

Introduction
A web application is a client-server software application in which the user interface runs in a web browser.Modern web applications today interact with databases , usually with a language called SQL. SQL (Structure Query Language) is a language designed for interacting with relational database management systems (RDBMS) like SQL Server, MySQL, Oracle, Sq Lite etc. In my earlier article, I explained about how to fix SQL network error 26.

What is THE RELATIONAL MODEL?

The Relational database management systems are based on relational model. The relational model for database management is an approach to managing data using a structure and language consistent with first-order predicate logic. It is a prescription for how to represent and manipulate data. The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and allow database management software ensure that it describes data structures for storing the data and retrieval procedures for answering queries.
Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. A table in an SQL database scheme corresponds to a predicate variable; the contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates.
Database designs are closely associated with database relationships, the association among two columns in a single or more tables.In SQL server, these relationships are defined using Primary Key-Foreign Key constraints. A link is created between two tables where the primary key of one table is associated with the foreign key of another table using database relationships.Now you must be wondering what is the key? Well Key is a set of one or more columns whose combined values are unique among all occurrences in a given table. A key is the relational means of specifying uniqueness.

What are the different types of relationships in SQL?

• One-to-One
• One-to-Many
• Many-to-Many

One-to-One Relationships

This is relationship between the primary and foreign keys relationships with primary key relating to foreign key only one record. A One-to-One relationship means that we have two tables that have a relationship, but that relationship only exists in such a way that any given row from Table A can have at most one matching row in Table B.
A real world example of this could be the relationship between a person and a drivers license. A person cannot have more than one driving licence and in reverse a licence cannot be owned by more than one person. This is a one-to-one relationship. Other examples could be: Person-to-mailing address , school-to-principal , person-to-passport,etc. It is called one-to-one because you can have only one record pointing to one other record.

A generic example of a one-to-one relationship.

One-to-Many Relationships

This is the most commonly used type of relationship.This is also relationship between primary & foreign keys relationships but here primary key relating to multiple records.The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table.
The common example of a one to many relationship is when we're talking about Users and Addresses. A User can have one or more addresses (mailing and billing address).The key here is that any one address can only belong to one user and only one user.

A one-to-many relationship from the perspective of TABLE B

Many-to-Many Relationships

In a Many-to-Many relationship, a record in Table B can potentially belong to several records in Table A. And vice versa, a record in Table A can potentially belong to several records in Table B. A simple many-to-many relationship includes two dimensions.
The many-to-many database relationship is used when the rows in the first table can map to multiple rows in the second table and those rows in the second table can also map to multiple different rows in the first table.

Common examples of Many-to-Many include:

• Doctors have many patients; patients have many doctors.
• Customers have many bank accounts; bank accounts might belong to more than one customer.
• Students are enrolled in many courses; each course has many students.

A typical example of a many-to-many relationship

Conclusion

There are, without a doubt, any number of ways in which you can relate the tables in these examples , depending on each table's role within a given database. The manner in which we use the examples here is not important; But the technique we use establish the relationship between these tables is. Once we learn these techniques, we can identify and establish relationships between the tables .

Relevant Reading

• Saving Changes not permitted in SQL Server
• The underlying provider failed on open
• How to Schedule Backup in SQL Server