SQL and Table Relationships
Tags: sql, ruby, database, datascience
Published: 2022-10-26 18:56:26

SQL, or Structured Query Language, is the standard language most programs use to CRUD data. Common database structures like SQL Server, Oracle, MongoDB, mySQL, etc use a table-like format with rows and columns to organize data, and since there will be multiple tables with differing representations, it is important to understand how to join and relate these tables together to have efficient, smoother data.

Some examples of SQL CRUD code include:
```
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
```
```
SELECT FirstName, LastName
FROM Persons
WHERE city="sacramento"
ORDER BY LastName ASC
```
```
UPDATE Persons
SET Address="N/A"
WHERE city="The Moon"
```
```
DELETE
FROM Persons
WHERE city="The Moon"
```
When planning to make multiple tables and numerous relationships in a schema, it is important for your tables to have a **primary key** when being created. A primary key is typically and ID that is used to keep track for linking entries between other tables. Once that ID is referenced in another table, it becomes a **foreign key**.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vdw643mjun4ess4wl659.png)

In this schema example, the first table showcases dog names as the primary key along with string inputs for the Dry/Wet and Good-Boy columns. The second table has tag numbers with certain dogs' measurements. When these primary keys merge with the final table at the bottom, they are referenced as foreign keys that reference an entirely different table when followed through. 

Depending on how these tables reference each other, you can imagine there would be different types of relationships.
There are two main types of relational databases in SQL

**One-To-Many** is fairly common in most schemas, and represents that a record in table A can be seen multiple times if joined to another table B. For example, if there is a large list of employees as Table B, and in that table has a foreign key of which supervisor advises a certain employee, you could imagine in a supervisors list as Table B, multiple supervisors would be referenced multiple times, spanning multiple employees.
**Many-To-Many** is very similar to one-to-many, but using the same example, it would be many-to-many if each employee could have more than one supervisor
**One-to-One** isn't as commonly seen as one-to-many, as you can simply just join the tables together, but it showcases that a singular record from table A with only relate to a singular record in table B, not duplicates on either pathway of the relation

When simplifying your schema, it is important to understand how to **join** your tables properly so the correct relationships and information is recorded over. A **join clause** combines entries from multiple tables, relative to a certain column that is shared between them.

There are 4 main types of joins

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8x5zb5qzqdjrdlp1u3fz.png)

The Inner join just returns a new table with matching columns from both tables.
The Left and Right joins will do the same but also include additional columns depending on which table was interpreted.
A Full Outer join just simply returns all the entries where a match is found on either table.

Sources
W3 Tutorials - SQL Joins 
(https://www.w3schools.com/sql/sql_join.asp)

101 Computing - Relational Databases
(https://www.101computing.net/relational-databases/)

SQLShack
(https://www.sqlshack.com/sql-definition/)

TheSupportGroup
(https://blog.supportgroup.com/getting-started-with-relational-databases-one-to-one-and-many-to-many-relationships)