Before building dashboards, writing SQL queries, or creating reports, it's essential to organize data in a way that accurately represents real-world entities while capturing their interactions. This process is known as data modeling.
What is a data model?
Picture a three-storey building, and let's call it the database building. Let's label each floor - schema. Since each floor has a unique identity, pair it with its corresponding floor number, so that the first floor becomes schema-1. Each floor or schema has four apartments, labelled table, paired with its unique unit number to become table-1, table-2, and so forth. Each apartment unit or table has a different characteristic defined by size and number of rooms. For instance, schema-1 can have studio units, while schema-2 can have one-bedroom units of different floor areas. Similarly, related tables in a database are hosted in the same schema. This organizational blueprint is referred to as a data model.
How is the Schema structured?
A schema typically takes two structures, dictated the relationships of the tables inside it.
1. The Star Schema.
As the name suggests, it takes the shape of a star. In this setup, there is a central table called the facts table that has a relationship with all the other tables called the dimension tables. A fact table explains what happened, while the dimension table gives context of who, where, when.
2. The Snowflake Schema.
This schema takes the shape of a snowflake. The dimension tables can have related subcategories, which are not directly linked to the fact table.
How are these tables related?
Every table in a database typically includes a column that uniquely identifies each record. This column is referred to as the primary key (PK). Think of it as a unique ID that distinguishes one row from another.
When one table needs to reference information stored in another table, it includes that table's primary key as one of its own columns. This referenced column is known as a foreign key (FK). Foreign keys establish relationships between tables, allowing the database to connect related information while maintaining consistency across the data.
Once tables contain primary keys and foreign keys, the next step is to define the relationships between them. Relationships tell the database how tables are connected, enabling it to combine related information when queries are executed. Think of relationships as the roads that connect tables; without these relationships, retrieving meaningful insights from multiple tables would be difficult and inefficient. Typically, there are three types of relationships:
1. One-to-One Relationship
This relationship suggests that each record in a table is associated with a single record in another table. For instance, a student can have only one identity card and vice versa. However, this relationship is not common.
2. One-to-Many Relationship
This implies that a single record in one table can be associated with multiple records in another table, while each record in the second table relates to only one record in the first. For example, one school can have many students, but each student can only belong to one school.
3. Many-to-Many Relationship
As the name suggests, records in both tables can be associated with multiple records in the other table. For instance, a teacher can teach many students, and a student can be taught by many teachers.
How is data retrieved across tables?
After organizing data in tables and relating them, it is then retrieved based on different use cases using joins. A join combines rows from two or more tables based on a common column, a primary key, and its corresponding foreign key. There are five types:
1. Inner join
This join returns only the records that have matching values from both tables. For instance, the syntax to view students' names (from student's table) and the respective schools (from the schools' table) they attend is as follows:
SELECT
s.student_name,
sch.school_name
FROM student_table s
INNER JOIN school_table sch
ON s.school_id = sch.school_id;
2. Left join
This join returns all records from the left table and any matching records from the right table. If no match exists, the columns from the right table are returned as NULL. The syntax will return every student, even if their school information is missing.
SELECT
s.student_name,
sch.school_name
FROM student_table s
LEFT JOIN school_table sch
ON s.school_id = sch.school_id;
3. Right join
This join reverses the left join. It returns all records from the right table and matching records from the left table. The syntax will return every school, even if no student is assigned to it.
SELECT
s.student_name,
sch.school_name
FROM student_table s
RIGHT JOIN school_table sch
ON s.school_id = sch.school_id;
4. Full outer join
This join returns all records from both tables. Matching rows are combined, while non-matching rows contain NULL values for the missing side. The syntax returns every record from both the students and school tables, whether a match exists or not.
SELECT
s.student_name,
sch.school_name
FROM student_table s
FULL OUTER JOIN school_Table sch
ON s.school_id = sch.school_id;
5. Anti joins
This join returns records that do not have a matching record in another table. However, anti join is not a SQL native command and can be implemented using the WHERE and the NOT NULL clauses. The syntax is as follows:
SELECT
s.student_name
FROM student_table s
LEFT JOIN school_table sch
ON s.school_id = sch.school_id
WHERE sch.school_id IS NULL;
Final Thoughts
Every dashboard and business application begins with a well-designed data model. Understanding how tables relate to one another and how SQL joins retrieve and combine the data across tables, bringing those relationships to life, is a fundamental skill in working with data.
Top comments (0)