REMS Data Model Documentation
Raizcorp Entrepreneurial Management System
1. Entity Relationship Overview
1.1 Core Entity Diagram (Conceptual)
┌─────────────────┐
│ Principals │
│ (Sponsors) │
└────────┬────────┘
│ 1
│
│ M
┌────────┴────────┐
│Principal Projects│
│ (Programmes) │
└────────┬────────┘
│ 1
│
│ M
┌──────────────┐ M ┌──────────────────┴───────────────────┐
│ Lead Types │─────────│ Entrepreneurs │
└──────────────┘ │ (Individual Participants) │
└──────────────────┬───────────────────┘
│ M
│
│ 1
┌──────────────────┴───────────────────┐
│ Entrepreneur Company │
│ (Business Entity) │
└──────────────────┬───────────────────┘
│ 1
│
│ M
┌──────────────────┴───────────────────┐
│ Entrepreneur Company Programme Years │
└──────────────────┬───────────────────┘
│ 1
│
│ M
┌──────────────────┴───────────────────┐
│ Entrepreneur Company Principal Projects│
└───────────────────────────────────────┘
2. Core Tables - Detailed Schema
2.1 Entrepreneurs
Purpose: Central entity storing individual entrepreneur data
| Field Name | Data Type | Size | Required | Description |
|---|---|---|---|---|
| Entrepreneur ID | Long | 4 | Yes (PK) | Auto-increment primary key |
| Reference Number | Long | 4 | No | External reference |
| Date Record Created | DateTime | 8 | No | Record creation timestamp |
| Lead Type ID | Long | 4 | No | FK to Lead Types |
| Division ID | Long | 4 | No | FK to Divisions |
| First Name | Text | 255 | No | First name |
| Surname | Text | 255 | No | Surname |
| Date of Birth | DateTime | 8 | No | Birth date |
| Gender | Text | 255 | No | Gender |
| Race | Text | 255 | No | Race/ethnicity |
| Identity Number | Text | 255 | No | SA ID number |
| SAID | Boolean | 1 | No | SA ID verified flag |
| Telephone Number | Text | 255 | No | Primary phone |
| Second Telephone Number | Text | 255 | No | Secondary phone |
| Email Address | Text | 255 | No | Primary email |
| Alternative Email Address | Text | 255 | No | Secondary email |
| SA Citizen | Text | 255 | No | Citizenship status |
| Province | Text | 255 | No | Province |
| Location | Text | 255 | No | City/town |
| Country | Long | 4 | No | FK to Country |
| Entrepreneur Company ID | Long | 4 | No | FK to Company |
| Shareholding Percentage | Double | 8 | No | Ownership % |
| Pipeline ID | Long | 4 | No | FK to Pipelines |
| Process Status ID | Long | 4 | No | FK to ProcessStatus |
| Process Status Notes | Text | 255 | No | Status notes |
| Date Status Changed | DateTime | 8 | No | Last status change |
| Status Modified By | Text | 255 | No | User who changed status |
| Distribution Model ID | Long | 4 | No | FK to Distribution Models |
| Delivery Location ID | Long | 4 | No | FK to Delivery Locations |
| Termination Reason ID | Long | 4 | No | FK to Termination Reasons |
| Unqualified Reason ID | Long | 4 | No | FK to Unqualified Reasons |
| Recommended Principal Project ID | Long | 4 | No | FK to Principal Projects |
| Recommended Product ID | Long | 4 | No | FK to Products |
| Recommended BBBEE Pillar | Text | 255 | No | BEE pillar recommendation |
| Allocation ID | Long | 4 | No | Allocation reference |
| Student ID | Text | 255 | No | Programme student ID |
| Style Questionnaire Score | Double | 8 | No | Assessment score |
| Style Questionnaire Results | Text | 255 | No | Assessment results |
| Personal Audit Score | Double | 8 | No | Audit score |
| Personal Audit Results | Text | 255 | No | Audit results |
| Urgency Result | Text | 255 | No | Urgency assessment |
| Financial Literacy Result | Double | 8 | No | Financial assessment |
| Entrepreneurial Assessment Results | Long | 4 | No | EA score |
| EA Date | DateTime | 8 | No | Assessment date |
| Purple Panel Score | Double | 8 | No | Panel score |
| PP Results | Long | 4 | No | Panel decision |
| Date Panel Held | DateTime | 8 | No | Panel date |
| Blue Heart Score Calculated | Double | 8 | No | Calculated score |
| Notes | Memo | - | No | General notes |
| Modified By | Text | 255 | No | Last modifier |
| Date Modified | DateTime | 8 | No | Last modification |
| SharePoint Link | Text | 255 | No | Document link |
EOI (Expression of Interest) Fields: | Field Name | Data Type | Description | |------------|-----------|-------------| | EOI SA Identity Number | Text | ID from application | | EOI Citizenship Type | Text | Citizenship claim | | EOI Business Name | Text | Business name from application | | EOI Business Address 1-3 | Text | Address lines | | EOI Business Postal Code | Text | Postal code | | EOI Industry ID | Long | Industry selection | | EOI Brief Description of Business | Memo | Business description | | EOI Enterprise Type | Text | Business type | | EOI Percent of Ownership | Double | Claimed ownership | | EOI Average Monthly Turnover | Text | Revenue range | | EOI Years in Business | Text | Business age |
Digital Selection Fields: | Field Name | Data Type | Description | |------------|-----------|-------------| | Access to PC | Boolean | Has PC access | | Access to Laptop | Boolean | Has laptop | | Access to Smartphone | Boolean | Has smartphone | | Access to Tablet | Boolean | Has tablet | | Quality of Connectivity | Long | Connection quality rating | | Digital State of Business | Long | Business digital readiness | | Digital Fulltime in Business | Long | Fulltime dedication | | Digital More Than One Partner | Long | Multiple partners | | Digital Bank Account | Long | Has business account | | Digital Comodities | Memo | Products/services | | Digital Motivation | Memo | Application motivation |
Indexes: - PrimaryKey (Entrepreneur ID) - UNIQUE - Delivery Location ID - Distribution Model ID - Division ID - Entrepreneur Company ID - Pipeline ID - Process Status ID - Recommended Principal Project ID - Recommended Product ID - Termination Reason ID - Unqualified Reason ID
2.2 Entrepreneur Company
Purpose: Business entities linked to entrepreneurs
| Field Name | Data Type | Size | Required | Description |
|---|---|---|---|---|
| Entrepreneur Company ID | Long | 4 | Yes (PK) | Auto-increment primary key |
| Company Status ID | Long | 4 | No | FK to CompanyStatus |
| Exit Notes | Text | 255 | No | Exit reason/notes |
| Date Status Changed | DateTime | 8 | No | Status change date |
| Status Modified By | Text | 255 | No | Modifier username |
| Company Delivery Location ID | Long | 4 | No | FK to Delivery Locations |
| Company Name | Text | 255 | No | Legal company name |
| Trading As | Text | 255 | No | Trade name |
| Business Address 1-3 | Text | 255 | No | Address lines |
| Business Postal Code | Text | 255 | No | Postal code |
| Website | Text | 255 | No | Company website |
| Industry ID | Long | 4 | No | FK to Industry |
| Brief Description of Business | Memo | - | No | Business description |
| Average Monthly Turnover | Text | 255 | No | Revenue range |
| Years in Business | Text | 255 | No | Business age |
| Date Business Started | DateTime | 8 | No | Start date |
| Permanent Employees | Double | 8 | No | Employee count |
| Temporary Employees | Double | 8 | No | Temp staff count |
| Enterprise Type | Text | 255 | No | Business type |
| CompanyReg | Text | 255 | No | Registration number |
| Business Registration Date | DateTime | 8 | No | Registration date |
| BEE Level | Long | 4 | No | BEE level (1-8) |
| BEE Certificate Type | Text | 255 | No | Certificate type |
| BEE Certificate Expire Date | DateTime | 8 | No | Expiry date |
| Percentage Black Owned | Double | 8 | No | Black ownership % |
| Percentage Women Black Owned | Double | 8 | No | Black women ownership % |
| Tax Clearance Certificate Number | Text | 255 | No | Tax clearance ref |
| Tax Clearance Expire Date | DateTime | 8 | No | Clearance expiry |
| VAT Registration Number | Text | 255 | No | VAT number |
| FYE Month | Text | 50 | No | Financial year end |
Insurance Fields: | Field Name | Type | Description | |------------|------|-------------| | Equipment Insurance | Boolean | Has coverage | | Equipment Insurance Amt | Double | Coverage amount | | Personal Vehicle Insurance | Boolean | Has coverage | | Personal Vehicle Insurance Amt | Double | Amount | | Business Vehicle Insurance | Boolean | Has coverage | | Stock Insurance | Boolean | Has coverage | | Life Insurance | Boolean | Has coverage | | Personal Medical Aid | Boolean | Has coverage | | Staff Medical Aid | Boolean | Staff coverage |
Indexes: - PrimaryKey (Entrepreneur Company ID) - UNIQUE - Company Delivery Location ID - Company Status ID - Industry ID
2.3 Principals
Purpose: Corporate sponsors/funders
| Field Name | Data Type | Size | Required | Description |
|---|---|---|---|---|
| Principal ID | Long | 4 | Yes (PK) | Auto-increment primary key |
| Principal | Text | 255 | No | Company name |
| Principal Address 1-4 | Text | 255 | No | Address lines |
| Channel Partner ID | Long | 4 | No | FK to Channel Partners |
| Industry ID | Long | 4 | No | FK to Industry |
| Industry Charter | Text | 255 | No | Industry charter |
| FYE | Text | 255 | No | Financial year end |
| Active | Boolean | 1 | No | Active status |
Contact Fields: | Field Name | Type | Description | |------------|------|-------------| | Contact First Name/Surname/Email | Text | Primary contact | | Marketing Contact * | Text | Marketing contact details | | Transformation Contact * | Text | Transformation contact | | Procurement Contact * | Text | Procurement contact |
2.4 Principal Projects
Purpose: Programmes/projects sponsored by Principals
| Field Name | Data Type | Size | Required | Description |
|---|---|---|---|---|
| Principal Project ID | Long | 4 | Yes (PK) | Auto-increment primary key |
| Principal ID | Long | 4 | No | FK to Principals |
| Sales Brief ID | Long | 4 | No | Sales brief reference |
| Sales Brief Number | Text | 255 | No | Brief number |
| Version Number | Double | 8 | No | Project version |
| Project Name | Text | 255 | No | Project name |
| Solutions Architect | Text | 255 | No | Assigned architect |
| Contract Type | Text | 255 | No | Contract type |
| Principal Project Status ID | Long | 4 | No | FK to Status |
| Brief Date | DateTime | 8 | No | Brief date |
| Contract Start Date | DateTime | 8 | No | Start date |
| Contract End Date | DateTime | 8 | No | End date |
| Contract Number | Text | 255 | No | Contract reference |
| Client Tier ID | Long | 4 | No | Client tier |
| Additional Marketing Costs | Double | 8 | No | Extra marketing budget |
| Standard Marketing Budget | Double | 8 | No | Base marketing budget |
| Active | Boolean | 1 | No | Active status |
| Pricing Mode | Long | 4 | No | Pricing model |
| Key Account Manager | Text | 255 | No | KAM assigned |
2.5 Products
Purpose: Programme products offered
| Field Name | Data Type | Size | Required | Description |
|---|---|---|---|---|
| Product ID | Long | 4 | Yes (PK) | Auto-increment primary key |
| Product | Text | 255 | No | Product name |
| Certificate Programme Name | Text | 255 | No | Certificate name |
| Product Code | Text | 255 | No | Product code |
| Pipeline ID | Long | 4 | No | FK to Pipelines |
| Programme Year Length | Double | 8 | No | Duration in years |
| Active | Boolean | 1 | No | Active status |
2.6 Pipelines
Purpose: Selection pipeline definitions
| Field Name | Data Type | Size | Required | Description |
|---|---|---|---|---|
| Pipeline ID | Long | 4 | Yes (PK) | Auto-increment primary key |
| Pipeline | Text | 255 | No | Pipeline name |
| Selection Type | Long | 4 | No | Selection type code |
2.7 ProcessStatus
Purpose: Steps within pipelines
| Field Name | Data Type | Size | Required | Description |
|---|---|---|---|---|
| Process Status ID | Long | 4 | Yes (PK) | Auto-increment primary key |
| Step Number | Long | 4 | No | Step sequence (0-8) |
| Progress Category ID | Long | 4 | No | FK to Progress Category |
| Status | Text | 255 | No | Status description |
| Status Type | Text | 255 | No | Status type |
| Time Unit | Long | 4 | No | Expected days |
| Active | Boolean | 1 | No | Active status |
| Sort Order | Double | 8 | No | Display order |
| Selection Type | Long | 4 | No | Selection type filter |
2.8 Lead Types
Purpose: Source and categorisation of leads
| Field Name | Data Type | Size | Required | Description |
|---|---|---|---|---|
| Lead Type ID | Long | 4 | Yes (PK) | Auto-increment primary key |
| Lead Type | Text | 255 | No | Lead type name |
| Lead Source | Text | 255 | No | Source category |
| Principal Project ID | Long | 4 | No | FK to Principal Projects |
| Lead Format | Text | 255 | No | Entry format |
| Industry ID | Long | 4 | No | Target industry |
| Start Date | DateTime | 8 | No | Active from |
| End Date | DateTime | 8 | No | Active until |
| Import Template | Text | 255 | No | Import template name |
| Status | Text | 255 | No | Lead type status |
| Do Not Use in Digital | Boolean | 1 | No | Exclude from digital |
3. Relationship Tables
3.1 Entrepreneur Status History
Purpose: Audit trail of status changes
| Field Name | Data Type | Description |
|---|---|---|
| Entrepreneur Status History ID | Long | PK |
| Entrepreneur ID | Long | FK to Entrepreneurs |
| Process Status ID | Long | FK to ProcessStatus |
| Process Status Notes | Text | Change notes |
| Date Status Changed | DateTime | Change timestamp |
| Status Modified By | Text | Username |
3.2 Entrepreneur Company Programme Years
Purpose: Company participation in programme years
| Field Name | Data Type | Description |
|---|---|---|
| Entrepreneur Company Programme Years ID | Long | PK |
| Entrepreneur Company ID | Long | FK to Company |
| Programme Year | Text | Year identifier |
| Status ID | Long | Programme status |
| Start Date | DateTime | Year start |
| End Date | DateTime | Year end |
3.3 Entrepreneur Company Principal Projects
Purpose: Links companies to specific principal projects
| Field Name | Data Type | Description |
|---|---|---|
| Entrepreneur Company Principal Projects ID | Long | PK |
| Entrepreneur Company Programme Years ID | Long | FK |
| Principal Project ID | Long | FK to Principal Projects |
| Allocation Date | DateTime | Allocation date |
3.4 Entrepreneur Notes
Purpose: Notes attached to entrepreneurs
| Field Name | Data Type | Description |
|---|---|---|
| Entrepreneur Notes ID | Long | PK |
| Entrepreneur ID | Long | FK to Entrepreneurs |
| Note Date | DateTime | Note date |
| Note Text | Memo | Note content |
| Created By | Text | Author |
3.5 Entrepreneur Purple Panelists
Purpose: Panel members for entrepreneur assessments
| Field Name | Data Type | Description |
|---|---|---|
| Entrepreneur Purple Panelists ID | Long | PK |
| Entrepreneur ID | Long | FK to Entrepreneurs |
| Panelist Name | Text | Panel member |
| Score | Double | Individual score |
4. Lookup Tables
4.1 List of Lookup Tables
| Table Name | Purpose | Key Fields |
|---|---|---|
| AreasofOperationLookup | Business operation areas | ID, Description |
| AssessmentMarkerLookup | Assessment markers | ID, Marker |
| BBBEEPillarLookup | BEE pillar options | ID, Pillar |
| BusinessPremisesLookup | Premises types | ID, Type |
| Channel Partners | Partner organisations | ID, Name |
| Client Tiers | Client tier levels | ID, Tier |
| CompanyStatus | Company status options | ID, Status |
| ContractTypeLookup | Contract types | ID, Type |
| CountryLookup | Countries | ID, Country |
| Delivery Locations | Service locations | ID, Location |
| DepartmentLookup | Departments | ID, Department |
| Distribution Models | Distribution types | ID, Model |
| Divisions | Business divisions | ID, Division |
| EnterpriseTypeLookup | Enterprise types | ID, Type |
| GenderLookup | Gender options | ID, Gender |
| IndustryLookup | Industry sectors | ID, Industry |
| LeadFormatLookup | Lead formats | ID, Format |
| LeadSourceLookup | Lead sources | ID, Source |
| PersonalAuditResultLookup | Audit results | ID, Result |
| ProgressCategoryLookup | Progress categories | ID, Category |
| ProvinceLookup | Provinces | ID, Province |
| RaceLookup | Race/ethnicity | ID, Race |
| ReplacementStatusLookup | Replacement statuses | ID, Status |
| SourceCategoryLookup | Source categories | ID, Category |
| SourceDetailLookup | Source details | ID, Detail |
| TerminationReason | Termination reasons | ID, Reason |
| TurnoverCategoriesLookup | Turnover ranges | ID, Range |
| UnqualifiedReason | Disqualification reasons | ID, Reason |
| UrgencyResultLookup | Urgency ratings | ID, Result |
| YearsinBusinessLookup | Business age ranges | ID, Range |
5. System Tables
5.1 DatabaseUsers
Purpose: Application user authentication
| Field Name | Data Type | Description |
|---|---|---|
| User ID | Long | PK |
| Username | Text | Login username (UNIQUE) |
| User Password | Text | Hashed password |
| LEVEL | Text | Access level |
| ForceChange | Boolean | Password change required |
| Delivery Location ID | Long | User's location |
| Department | Text | User's department |
| Email Address | Text | User email |
| First Name | Text | First name |
| Surname | Text | Surname |
| Cell Phone Number | Text | Mobile number |
5.2 DatabaseUsers Module Permissions
Purpose: Feature-level access control
| Field Name | Data Type | Description |
|---|---|---|
| Permission ID | Long | PK |
| User ID | Long | FK to DatabaseUsers |
| Module ID | Long | Feature/module code |
| Permission Level | Long | Access level |
6. Temporary Tables
Used for report generation:
| Table Name | Purpose |
|---|---|
| TempLeadsReport | Digital lead reports |
| Temp Entrepreneur Steps | Pipeline step analysis |
| Temp Purple Meeting Status | Purple panel prep |
| Temp Lead Type for Lead Report | Lead type filtering |
| Temp Principal Project for Lead Report | Project filtering |
7. Archive Tables
| Table Name | Purpose |
|---|---|
| Archive Entrepreneur Data | Archived entrepreneur records |
| Archive Entrepreneur Purple Panelists | Archived panel data |
| Restore Deleted Leads | Deleted lead recovery |
8. Key Relationships Summary
| Parent Table | Child Table | Relationship | FK Field |
|---|---|---|---|
| Entrepreneurs | Entrepreneur Status History | 1:M | Entrepreneur ID |
| Entrepreneurs | Entrepreneur Notes | 1:M | Entrepreneur ID |
| Entrepreneurs | Entrepreneur Purple Panelists | 1:M | Entrepreneur ID |
| Entrepreneur Company | Entrepreneurs | 1:M | Entrepreneur Company ID |
| Entrepreneur Company | EC Programme Years | 1:M | Entrepreneur Company ID |
| EC Programme Years | EC Principal Projects | 1:M | EC Programme Years ID |
| Principals | Principal Projects | 1:M | Principal ID |
| Principal Projects | Lead Types | 1:M | Principal Project ID |
| Principal Projects | Entrepreneurs (Recommended) | 1:M | Recommended Principal Project ID |
| Pipelines | Entrepreneurs | 1:M | Pipeline ID |
| Pipelines | Products | 1:M | Pipeline ID |
| ProcessStatus | Entrepreneurs | 1:M | Process Status ID |
| Lead Types | Entrepreneurs | 1:M | Lead Type ID |
| Divisions | Entrepreneurs | 1:M | Division ID |
| Delivery Locations | Entrepreneurs | 1:M | Delivery Location ID |
| Distribution Models | Entrepreneurs | 1:M | Distribution Model ID |
Document generated by BMAD Document Project Workflow