Mapping Relationships

Customer Orders

Foreign Key: orders.customer_id references customers.customer_id

Graph Relationship:

(Customer)-[:PLACED]->(Order)

The customer places an order. The relationship direction reflects the business action.

Employee Processes Order

Foreign Key: orders.employee_id references employees.employee_id

Graph Relationship:

(Employee)-[:PROCESSED]->(Order)

Alternatively, use (Order)-[:PROCESSED_BY]→(Employee) if you mostly query "which employee processed this order?" instead of "which orders did this employee process?"

Order Shipped By

Foreign Key: orders.ship_via references shippers.shipper_id

Graph Relationship:

(Order)-[:SHIPPED_BY]->(Shipper)

Order Contains Products

Foreign Key: order_details.order_id references orders.order_id and order_details.product_id references products.product_id

This junction table becomes a relationship with properties:

Graph Relationship:

(Order)-[:CONTAINS {quantity, unitPrice, discount}]->(Product)

The order_details table data becomes properties on the CONTAINS relationship.

Product Categorization

Foreign Key: products.category_id references categories.category_id

Graph Relationship:

(Product)-[:IN_CATEGORY]->(Category)

Product Supplier

Foreign Key: products.supplier_id references suppliers.supplier_id

Graph Relationship:

(Supplier)-[:SUPPLIES]->(Product)

Or alternatively: (Product)-[:SUPPLIED_BY]→(Supplier)

Employee Hierarchy

Foreign Key: employees.reports_to references employees.employee_id

Graph Relationship:

(Employee)-[:REPORTS_TO]->(Employee)

This self-referencing relationship creates the management hierarchy.

Scenario 1: Foreign Keys to Small Lookup Tables

When a foreign key references a small lookup table with static values, consider using a property instead.

Example: A priority_id foreign key to a priorities table:

-- Relational model
SELECT o.order_id, p.priority_name
FROM orders o
JOIN priorities p ON o.priority_id = p.priority_id;

Option A - As a relationship (more complex):

(Order)-[:HAS_PRIORITY]->(Priority {name: "High"})

Option B - As a property (simpler):

(Order {priority: "High"})

Choose Option B when:

Scenario 2: Redundant Relationships

Sometimes a relationship can be inferred from other relationships and does not need to be stored.

Example: If you have:

(Customer)-[:PLACED]->(Order)-[:CONTAINS]->(Product)

You might be tempted to also create:

(Customer)-[:PURCHASED]->(Product)

But this is redundant - you can always find what products a customer purchased by traversing through orders. Only create direct relationships if:

Scenario 3: Self-Referencing Foreign Keys That Represent Hierarchy Levels

Sometimes self-referencing tables encode fixed hierarchy levels rather than flexible relationships.

Example: An employees table with level_1_manager_id, level_2_manager_id, level_3_manager_id columns.

Instead of creating three different relationship types, consider:

Scenario 4: Foreign Keys for Audit/Metadata

Foreign keys that track who created or modified records might not need relationships.

Example: created_by_user_id and modified_by_user_id columns:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    created_by_user_id INTEGER,
    modified_by_user_id INTEGER,
    -- ... other columns
);

Consider: