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.
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
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)
Top comments (2)
Sorry Justin, I'm going to have to pull you up on a number of important points.
The Primary Key is a field or fields that uniquely and forever identifies an entitty. Ideally this should be a real-world piece of data that is idempotent (a fancy word meaning it cannot be changed). Where a suitable real world key is not available then we will often use an ID field (but you should attempt to identify a business key first)
A table in a relational database represents an entity (physical or conceptual) and a row in the table represents an instance of that entity. All of the columns should either be part of the key or be dpendent on the key, the whole key and nothiing but the key. If they do not then they represent a different entity and should be modelled separately.
In your table diagram there are a number of issues. 1) name is not a good primary key for the dogs because you may have many dogs with the same name (and the primary key cannot be duplicated) or the dog's name may change if it changes owners. 2) all three tables contain attributes that relate directly to the dog and so should be in a single table (unless the height and weight indicate the size of the tag, in which case the dog is going to struggle to walk!) 3) you are storing Age which is a field that is a temporal dependency. In a years time Bubbles will no longer be 5. You should instead be storing the date of birth and calculating the age when required. 4) type of food, breed and color should all be reference tables and the PK identifier from the reference table used instead of the descriptive attribute, otherwise you have to add constraints to the table (e.g. can a dog eat both kinds of food, is White/Brown different from Brown/White ?)
Inner Join will return records where there is a match in both tables
Left/Right Join will return all records from the Left/Right table and details from the Right/Left table if they exist. NOTE in 30 years of working with SQL I have never needed a Right Join in production - it is a code-smell that you don't understand the data
Full Outer Join will give you ALL records from BOTH tables, but put them on the same row if there is a match
Many:Many relationships will likely need a bridging table which you have not mentioned. In your example of employees having many supervisors you would have a table of Employees, a table of Supervisors (assuming they were not a self referencing hierarchical table where supervisors were a sub-set of employees) and a bridging table which details which employees had which supervisors.
One:One relationships are rare and are often a code-smell as they represent more attributes of the same entity (so should be in the same table). There are good reasons for splitting them out into a separate extension table, for example where they would be sparsley populated or are not relevant for all instances of an entity. For example you have a product table (ID, Name, Description, Product Group) and you sell different types of product (e.g. Wine, books, clothing). Each of these product groups would have different attributes so rather than have 200 columns of which only a handful are populated on each entity, you might have a WineProduct table with ID, Terroir, colour, tasing notes and a BookProduct table with ID, Author, release date, page count.
You have also lnot mentioned Cartesian Join which is where two tables are not related and you end up with a map (hence Cartesian) of all records in table A x All records in table B. You would typically use this where you want to generate a set of data so you have every data point which may or may not exist in the source database. For example Cartesian join Store locations to a calendar table so that we have a record for every day for every store, then Left Join that to the sales table, so that even if a store had no sales on a day, we still get the day/store datapoint.
Sorry to be a pedant, but badly structured data will bite you for a very very long time.
nice intro sql