Data Modelling,Relationships and Joins.

Olá a todos os membros do **webmastersmz.com**!

Como especialista em tecnologia, analisei o tópico sobre **Modelagem de Dados, Relacionamentos e Joins**, e gostaria de partilhar algumas reflexões técnicas que são fundamentais para qualquer projecto web robusto que estejamos a desenvolver aqui em Moçambique.

### Pontos principais da análise:

1.  **Modelagem de Dados (Data Modelling):** A base de qualquer sistema é o esquema da base de dados. Uma modelagem eficiente, seguindo as formas normais (1NF, 2NF, 3NF), é crucial para evitar a redundância de dados e garantir a integridade. Errar na modelagem inicial custa muito caro em escalabilidade futura.
2.  **Relacionamentos (Relationships):** Compreender a fundo as cardinalidades (Um-para-Um, Um-para-Muitos, Muitos-para-Muitos) define como o seu sistema lida com a complexidade. É vital desenhar as chaves estrangeiras (*foreign keys*) com rigor para manter o relacionamento entre entidades coerente.
3.  **Joins:** Este é o ponto onde muitos desenvolvedores perdem performance. O uso indiscriminado de `JOINs` complexos em tabelas sem índices adequados é a receita para uma aplicação lenta. Devemos sempre priorizar a indexação nas colunas utilizadas nas cláusulas `ON` e `WHERE`. O entendimento da diferença entre `INNER JOIN`, `LEFT JOIN` e `RIGHT JOIN` é o que separa um código funcional de um código optimizado.

**Convite ao Debate:**
Para os colegas desenvolvedores que frequentam este fórum: qual tem sido a vossa maior dor de cabeça na modelagem de bases de dados relacionais em sistemas de alto tráfego? Vocês preferem utilizar ORMs (como Eloquent ou Doctrine) que abstraem essas camadas, ou mantêm-se fiéis ao SQL puro para garantir o máximo desempenho? Vamos trocar ideias sobre as melhores práticas que utilizam nos vossos projectos actuais.

***

Para garantir que os vossos projectos e fóruns rodam sem falhas, convido-vos a conhecer as soluções de alojamento de alta performance da **AplicHost** em https://aplichost.com.

Data Modelling,Relationships and Joins.



Tópico: Data Modelling,Relationships and Joins.
Categoria: Tutoriais | Programação & Tecnologia
Idioma Principal: Português (Conteúdo de Tecnologia)

Descrição do Conteúdo / Informações:
-------------------------------------------------------------------------


Introduction


Data modelling is the process of defining tables and establishing their relationships.The benefits of data modelling is that it simplifies data analysis expressions,speeds up query perfomance and reduces complexicity.

Flat table is a single table containing all dimensions and facts. Combines all of your data into one place containing all attributes

It is suitable for a small,simple dataset where data has few rows.

Advantages of a flat table

It is easy to understand

Appropriate for small databases

Easy to import and analyze.

Requires few or no relationships

Disadvantages include

Creates data redudancy-where the same customer,product or category information may be repeated many times.

The table may become very wide and difficult to maintain as the dataset grows.

Star schema - centralized fact table surrounded by single dimension tables.

It organizes data into two distinct table types to form a star like structure.

An example of a star schema above,we can see the fact table being sales table and the rest being dimensional tables.

Advantages

Fast performance

Simple DAX formulas and clear pathways.

_Disadvantages

_

Redudancy within dimensions since dimension tables are denormalized

Requires light upfront preparation since raw data does not come arranged as a star schema.

Intergrating data measured at different detail into a star schema requires creating separate fact tables or careful model design.

A star schema is suitable for systems with large volumes of transactional data where performance and file size matter.

I recommend a star schema with one to many cardinalities and single direction filtering.

Snowflake schema-normalized extension of a star schema where dimension tables is normalized and connects to other dimension tables.

Advantages :

Easier to update attributes in one place.

Saves storage in relational database

Reduces duplicates dimensional values.

Disadvantages : increase DAX complexicity as it requires multiple joins steps.

Degrades power bi performance due to chain filtering and relationships.

The difference between star and snowflake schema is that Snowflake schema data is split into sub-dimensional tables to eliminate duplicate data therefore expanding hierarchical branches while the star schema each dimension is a single,flat table connected directly to the fact table.

Fact tables and dimension tables

Fact tables -contains mostly keys,dates and numerical metrics eg patients_id,doctors_id,procedure_id

Dimension tables- contains descriptive attributes used to slice,dice,filter and group facts.It mostly describes and gives more of the context around the facts

Granularity-The exact level of detail representation by a singe row in afact table eg individual item line vs daily total transactional summary

Key entities:

Facts:fact sales,fact orders,fact inventory

Dimensions:dimcustomer,dimproduct,dim location

Cardinalities in data modelling/creating relationships-this refers to the raw data in one table in relation to data in another table based on numerical count of matching rows.

Many to one(:1) this relationship occurs mostly when connecting from the fact table to the dimension table. The foreign key in the fact table connecting with the primary key in the dimension table.

One to many(1:)Primary key in the dimension table connects to foreign key in fact table.

One to one(1 : 1)connected using one primary key in the fact table to another primary key in the dimensional table or vice versa.

Many to many(* : *)used when bridge tables or direct relationships handle non unique keys on both sides.eg a foreign key and another foreign key.

Primary Key-is the main unique column identifier for each row in a table,it is exactly one per table eg customer id in dimcustomerexample C100)

Unique keys-ensures values in a secondary column are unique across all rows eg emailaddress,dimcustomers.

Foreign key non unique identifier column in a fact table linking back to a dimension.

Referential integrity-ensuring foreign key values in the fact table exist in the referenced dimension table.

Active vs inactive-only active filter path can exist between two tables at a time.

We activate relationships on the home tab>manage relationships>status>active

Inactive paths are activated using DAX functions like USERLATIONSHIP()and are dotted lines.

Filter direction

Single direction-filter flows strictly from the one side dimensionn to the many side(fact).This is the default.

Bi-directional(Both)-filters flow in both directions across tables.

Some disadvantages of bi-directional filters are that they may cause perfomance drops,ambigous filtering paths and unexpected visual results.

Joins in power query

select the join kind and click ok

Left outer-Keeps all rows from left table or table 1 and matching rows from table two.

Right Outer -keeps all rows from the right table or table two and the matching rows from table one.

Full outer-keeps all rows from both tables,combining matches and leaving nulls for non matches.

Inner-keeps only rows where the join keys match in both tables

Left anti-Keeps rows from table 1 that have no match in table two.Great to use for finding missing records.

Right anti-Keeps rows from table two and those that have no match in table one.

MERGE AND APPEND IN POWER QUERY.

Merge-this is basically physically combining two physical tables into a single wide table.

When to merge?we merge when combining staging lookup tables before loading eg merging product subcategory into product category

Append-We do this by stacking rows of tables on top of each other.This makes the table longer.The condition is that the table must have same exact number of columns and be the same type of columns.

append command below merge queries is used to stack or combine rows together,rows with exact number columns.



Conclusion


Understanding data modelling,relationships and joins is the foundation of an effective data analysis.

Understanding the flat tables and relationships simplifies my DAX functions and power query which speeds up the query perfomance. Understanding the right join type to use and establishing a clear cardinality makes it easier to model my data.