Relationships

1. Cardinality

Cardinality describes the type of relationship between the two tables.

Types of Cardinality:

Type	Description	Example
Many to One (*:1)	Default and most common. Many rows in one table relate to one row in another.	Sales → Products: Many sales records relate to one product.
One to One (1:1)	Each row in one table matches exactly one row in the other.	Employees ↔ Employee Details: One employee has one detail row.
One to Many (1:*)	The reverse of Many to One — often used in visualizations.	Departments → Employees: One department has many employees.
Many to Many (:)	Used when both sides have duplicates and there’s no clear "one" side.	Students ↔ Courses: Students can enroll in many courses, and courses can have many students.

2. Cross-filter direction

This controls how filters flow between related tables.

Types of Cross-filter Direction:

Type	Description	Example
Single	Filters flow one way only (usually from lookup table to fact table).	Products → Sales: Filter a product to see its sales.
Both	Filters flow both ways — enables filtering in either direction	Useful in complex models (e.g., many-to-many relationships).
None	No filtering across the relationship.	Rarely used unless you're managing filtering manually.

Real-World Example:

You have these tables:

• Products (ProductID, Name, Category)

• Sales (SaleID, ProductID, Quantity)

Cardinality = Many to One, because many sales can be for one product.

Cross-filter direction:

• Single → You can filter Sales by Products (e.g., show sales of "Laptops").

• Both → You could also filter Products based on what’s sold (e.g., show only products that had sales).

Tips:

• Use Single by default for performance and clarity.

• Use Both only when needed (like in many-to-many scenarios or complex filtering).

• Be careful with Both - it can create circular dependencies.