Advanced Data Modeling Patterns

-- Example: Sales Fact Table
CREATE TABLE fact_sales (
  sale_id INT PRIMARY KEY,
  product_id INT,
  customer_id INT,
  store_id INT,
  date_id INT,
  quantity INT,
  unit_price DECIMAL(10,2),
  total_amount DECIMAL(12,2),
  created_at TIMESTAMP
);

-- Example: Product Dimension
CREATE TABLE dim_product (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  category VARCHAR(50),
  brand VARCHAR(50),
  color VARCHAR(30),
  size VARCHAR(20),
  is_active BOOLEAN,
  effective_date DATE,
  expiry_date DATE
);

-- SCD Type 2 Implementation
CREATE TABLE dim_customer_scd2 (
  customer_key INT PRIMARY KEY,
  customer_id INT,
  customer_name VARCHAR(100),
  email VARCHAR(100),
  city VARCHAR(50),
  effective_date DATE,
  expiry_date DATE,
  is_current BOOLEAN,
  version INT
);

-- Insert new version when customer data changes
INSERT INTO dim_customer_scd2 
SELECT 
  nextval('customer_key_seq'),
  customer_id,
  customer_name,
  email,
  city,
  CURRENT_DATE as effective_date,
  '9999-12-31' as expiry_date,
  true as is_current,
  version + 1
FROM dim_customer_scd2 
WHERE customer_id = ? AND is_current = true;

-- Customer Hub
CREATE TABLE hub_customer (
  customer_hk CHAR(32) PRIMARY KEY, -- Hash key
  customer_id VARCHAR(50) UNIQUE NOT NULL, -- Business key
  load_date TIMESTAMP NOT NULL,
  record_source VARCHAR(100) NOT NULL
);

-- Product Hub
CREATE TABLE hub_product (
  product_hk CHAR(32) PRIMARY KEY,
  product_id VARCHAR(50) UNIQUE NOT NULL,
  load_date TIMESTAMP NOT NULL,
  record_source VARCHAR(100) NOT NULL
);

-- Customer-Product Link (e.g., purchases)
CREATE TABLE link_customer_product (
  link_hk CHAR(32) PRIMARY KEY,
  customer_hk CHAR(32) NOT NULL,
  product_hk CHAR(32) NOT NULL,
  load_date TIMESTAMP NOT NULL,
  record_source VARCHAR(100) NOT NULL,
  FOREIGN KEY (customer_hk) REFERENCES hub_customer(customer_hk),
  FOREIGN KEY (product_hk) REFERENCES hub_product(product_hk)
);

-- Order Link
CREATE TABLE link_order (
  link_hk CHAR(32) PRIMARY KEY,
  order_hk CHAR(32) NOT NULL,
  customer_hk CHAR(32) NOT NULL,
  order_date_hk CHAR(32) NOT NULL,
  load_date TIMESTAMP NOT NULL,
  record_source VARCHAR(100) NOT NULL
);

-- Customer Satellite
CREATE TABLE sat_customer (
  sat_hk CHAR(32) PRIMARY KEY,
  customer_hk CHAR(32) NOT NULL,
  customer_name VARCHAR(100),
  email VARCHAR(100),
  phone VARCHAR(20),
  address TEXT,
  load_date TIMESTAMP NOT NULL,
  load_end_date TIMESTAMP,
  record_source VARCHAR(100) NOT NULL,
  hash_diff CHAR(32) NOT NULL, -- For change detection
  FOREIGN KEY (customer_hk) REFERENCES hub_customer(customer_hk)
);

-- Product Satellite
CREATE TABLE sat_product (
  sat_hk CHAR(32) PRIMARY KEY,
  product_hk CHAR(32) NOT NULL,
  product_name VARCHAR(100),
  description TEXT,
  category VARCHAR(50),
  brand VARCHAR(50),
  price DECIMAL(10,2),
  load_date TIMESTAMP NOT NULL,
  load_end_date TIMESTAMP,
  record_source VARCHAR(100) NOT NULL,
  hash_diff CHAR(32) NOT NULL,
  FOREIGN KEY (product_hk) REFERENCES hub_product(product_hk)
);

// Neo4j Cypher - Define entity types and relationships
// Create constraints for entity types
CREATE CONSTRAINT person_id IF NOT EXISTS FOR (p:Person) REQUIRE p.id IS UNIQUE;
CREATE CONSTRAINT company_id IF NOT EXISTS FOR (c:Company) REQUIRE c.id IS UNIQUE;
CREATE CONSTRAINT product_id IF NOT EXISTS FOR (p:Product) REQUIRE p.id IS UNIQUE;

// Create indexes for better performance
CREATE INDEX person_name IF NOT EXISTS FOR (p:Person) ON (p.name);
CREATE INDEX company_name IF NOT EXISTS FOR (c:Company) ON (c.name);

// Example: Person node with properties
CREATE (p:Person {
  id: "P001",
  name: "John Doe",
  email: "john.doe@email.com",
  age: 30,
  location: "New York",
  skills: ["Python", "Data Science", "Machine Learning"],
  created_at: datetime(),
  is_active: true
});

// Example: Company node with properties
CREATE (c:Company {
  id: "C001",
  name: "TechCorp",
  industry: "Technology",
  founded_year: 2010,
  employee_count: 500,
  revenue: 10000000,
  location: "San Francisco"
});

// Create relationships with properties
MATCH (p:Person {id: "P001"}), (c:Company {id: "C001"})
CREATE (p)-[r:WORKS_FOR {
  role: "Data Scientist",
  start_date: date("2022-01-15"),
  is_current: true,
  department: "Engineering",
  salary: 120000
}]->(c);

// Create relationship between products and categories
MATCH (p:Product {id: "PRD001"}), (cat:Category {name: "Electronics"})
CREATE (p)-[:BELONGS_TO {
  confidence: 0.95,
  assigned_by: "system",
  assigned_date: datetime()
}]->(cat);

// Create temporal relationship for purchases
MATCH (p:Person {id: "P001"}), (prod:Product {id: "PRD001"})
CREATE (p)-[r:PURCHASED {
  purchase_date: datetime("2024-01-15T10:30:00"),
  quantity: 2,
  unit_price: 29.99,
  total_amount: 59.98,
  payment_method: "credit_card"
}]->(prod);