¶ Multi-Datasource Architecture
¶ Overview
Elementrix v2.0 introduces multi-datasource support, allowing data products to be built from multiple tables across multiple databases. This enables complex data products that join and transform data from disparate sources.
¶ Supported Databases
| Database | Versions | Driver |
|---|---|---|
| PostgreSQL | 11.x - 17.x | org.postgresql.Driver |
| MySQL | 5.7 - 8.x | com.mysql.cj.jdbc.Driver |
| MariaDB | 10.5 - 10.11+ | org.mariadb.jdbc.Driver |
| Oracle | 12c+ | oracle.jdbc.OracleDriver |
| SQL Server | 2016+ | com.microsoft.sqlserver.jdbc.SQLServerDriver |
¶ Architecture
¶ Connection Management
Multi-datasource architecture allows connecting to multiple databases simultaneously:
┌─────────────────────────────────────────────────────────────┐
│ Data Product │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Unified Schema │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ Table A │ │ Table B │ │ Table C │ │ │
│ │ │ (Join) │ │ (Join) │ │ (Lookup) │ │ │
│ │ └──────┬──────┘ └──────┬──────┘ └──────┬──────┘ │ │
│ └─────────┼───────────────┼───────────────┼──────────┘ │
│ │ │ │ │
└────────────┼───────────────┼───────────────┼────────────────┘
│ │ │
┌────────▼────────┐ ┌────▼────────┐ ┌────▼────────┐
│ PostgreSQL │ │ MySQL │ │ Oracle │
│ (Customer) │ │ (Orders) │ │ (Products) │
└─────────────────┘ └─────────────┘ └─────────────┘
¶ Schema Discovery
The system automatically discovers:
- Tables and views
- Column names and data types
- Primary keys
- Foreign key relationships
- Indexes
¶ Relationship Mapping
¶ Auto-Detect FK Relationships
The system can automatically detect foreign key relationships:
- Database-level FK constraints
- Naming conventions (e.g.,
user_id→users.id) - Same-name columns across tables
¶ Manual Relationship Configuration
For complex relationships, manually configure:
- Join type (INNER, LEFT, RIGHT, FULL)
- Source and target columns
- Relationship cardinality
¶ ER Diagram Builder
¶ Visual Interface
The ER Diagram Builder provides a visual interface for:
- Viewing all connected tables
- Seeing relationships between tables
- Editing join configurations
- Previewing the final schema
¶ Features
┌─────────────────────────────────────────────────────────────┐
│ ER Diagram Builder │
├─────────────────────────────────────────────────────────────┤
│ ┌─────────────┐ ┌─────────────┐ │
│ │ customers │──1:N────│ orders │ │
│ │─────────────│ │─────────────│ │
│ │ id (PK) │ │ id (PK) │ │
│ │ name │ │ customer_id │ │
│ │ email │ │ order_date │ │
│ │ created_at │ │ total │ │
│ └─────────────┘ └──────┬──────┘ │
│ │ │
│ 1:N │ │
│ ▼ │
│ ┌─────────────┐ │
│ │ order_items │ │
│ │─────────────│ │
│ │ id (PK) │ │
│ │ order_id │ │
│ │ product_id │ │
│ │ quantity │ │
│ └─────────────┘ │
└─────────────────────────────────────────────────────────────┘
¶ Join Types
| Join Type | Description | Use Case |
|---|---|---|
| INNER | Only matching rows | Required relationships |
| LEFT | All from left + matching | Optional relationships |
| RIGHT | All from right + matching | Reverse optional |
| FULL | All rows from both | Complete data merge |
¶ Configuration
¶ Adding Datasources
- Navigate to Data Product → Data Sources
- Choose data sources
- Save Datasource
¶ Selecting Tables
- Choose datasource from dropdown
- Browse available tables/views
- Select tables to include
- Configure join relationships
- Preview the combined schema
¶ Best Practices
¶ Performance
- Use read replicas for large tables
- Add indexes on join columns
- Limit selected columns
- Use appropriate join types
- Consider materialized views for complex queries
¶ Security
- Use dedicated readonly users
- Rotate credentials regularly
- Enable SSL/TLS for all connections
- Use connection pooling
- Monitor access logs
¶ Data Quality
- Validate referential integrity
- Check for orphaned records
- Monitor query performance
- Set up data freshness alerts