How I Analyse Database Table Relationships Before Starting Analysis

When I am introduced to a new project, one of my first priorities is understanding the Data Landscape, means how the Data is spread across. The sources are mostly from Excel and SQL databases for the structured data. 

Before understanding the KPIs, dashboard behaviour, I'll try to understand below things

• What tables exist? (Must explain List of Tables and Overview of each)
• What does each table represent? (Must explain in detail about each table)
• Which columns uniquely identify a record? (Keys present in the table or not to establish relations)
• Are relationships already defined? (To see if there's connectivity established or not)

Note: There’s still a lot more to explore and analyze, but for this post, we’ll stop here. This is only the beginning — there’s much more analysis to come. 

Here is the approach I follow for a database.

1. Start with Data Discovery — Understand What Exists

My first step is going into the appropriate correct shcema/database and creating an inventory of the available tables rather than immediately querying individual datasets.

Note: INFORMATION_SCHEMA is a built-in directory that tells about the database itself, rather than the actual data stored in tables.

• Checking what are the databases available in the process

SHOW DATABASES;

SELECT *

FROM INFORMATION_SCHEMA.SCHEMATA;

• Then I'll make the exact database default, to easily query the Table without explicitly defining database each time.

USE database_name;

• Listing all the Tables present in the selected default database

SHOW TABLES;

SELECT *

FROM INFORMATION_SCHEMA.TABLES

WHERE table_owner NOT LIKE "System user";

 2. Understand the Structure of Each Table

The next step is examining the columns, data types, nullability, and naming patterns of each table. 

From the columns and naming I can already begin asking/understanding business questions. I'll see this is where technical data understanding starts connecting with business understanding.

• Listing Column metadata from the table

SELECT

table_catalog,

table_schema,

TABLE_NAME,

COLUMN_NAME,

DATA_TYPE,

IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'sample_table';

3. Checking for Keys/Constraints

This will explain us that What uniquely represents one record in this table, What records responsible for maintain connectivity between tables.

• This will give you any keys present in the table

SELECT *

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME = 'sample_table';

• We can view the table configuration to confirm key constraints as well.

SHOW CREATE TABLE sample_table;

• So the output result that of similar columns in both tables but one table has Primary key and another table has Foreign key means that we can establish relation between them
  
  
• Then we proceed to validate our primary key column to make sure it's unique or not.

select

count(*) as total_id,

count(distinct(id)) as unique_total_id

from sample_table;

Note: Primary key uniquely identifies each record and Foreign Key reffering to Which record in another table does this belong to. A primary key can also be a foreign key.

3.1. Checking for Keys/Constraints

Not every table has one column that uniquely identifies a row. 

For Example, "order_id" will naturally repeat because one order can contain multiple products. "product_id" will repeat because one product can appear in multiple orders.

But perhaps: order_id + product_id uniquely identifies a record. This can be validated using view create table.

4. Check Whether Relationships Are Already Defined

To confirm that both tables can establish relation, we can try to join both tables. For below code we can find broken foreign keys as well.

• No rows returned → every foreign-key value has a matching primary-key value 
• Rows returned → those values exist in the foreign-key table but don't have a matching primary key

SELECT A.id, B.id FROM A JOIN B ON A.id = B.id;

WHERE c.customer_id IS NULL;

Business Analyst or Data Analyst perspective, understanding database relationships can make them to ask/analyze exact requirements where data understanding and business understanding meet.  

This is one simple practical approach, but not the only one. There are often multiple ways to explore a database, validate relationships, profile data, and understand the underlying business model.

I’d be interested to learn how other Business Analysts, Data Analysts, Data Engineers, and BI professionals approach this during project onboarding.

If you use a different method, tool, SQL query, or framework, feel free to share it in the comments. Knowledge grows faster when we compare approaches and learn from each other.