Data Model

​

Entities and Attributes

​

1. CustomFieldSection

• Primary Key: id
• Unique Constraint: (entity_type, code, tenant_id) (if tenant_id is present)
• Foreign Key: tenant_id references tenants.id (if multitenancy is enabled)
• Indexes: tenant_id (for query optimization)

​

2. CustomField

• Primary Key: id
• Unique Constraint: (code, entity_type, tenant_id) (if tenant_id is present)
• Foreign Keys:
  • tenant_id references tenants.id (if multitenancy is enabled)
  • custom_field_section_id references custom_field_sections.id
• Indexes: tenant_id, custom_field_section_id (for query optimization)

​

3. CustomFieldOption

• Primary Key: id
• Unique Constraint: (custom_field_id, name, tenant_id) (if tenant_id is present)
• Foreign Keys:
  • tenant_id references tenants.id (if multitenancy is enabled)
  • custom_field_id references custom_fields.id (with CASCADE ON DELETE)
• Indexes: tenant_id, custom_field_id (for query optimization)

​

4. CustomFieldValue

• Primary Key: id
• Unique Constraint: (entity_type, entity_id, custom_field_id, tenant_id) (if tenant_id is present)
• Foreign Keys:
  • tenant_id references tenants.id (if multitenancy is enabled)
  • custom_field_id references custom_fields.id (with CASCADE ON DELETE)
• Indexes:
  • tenant_id, entity_type, entity_id, custom_field_id (for query optimization)
• Polymorphic Association: Uses entity_type and entity_id to associate with any entity in the application.

​

Relationships and Associations

• CustomFieldSection (1) --- (M) CustomField
  • Type: One-to-Many
  • Foreign Key in CustomField: custom_field_section_id references custom_field_sections.id
  • On Delete: Application-defined (nullable foreign key allows for fields without sections)
• CustomField (M) --- (1) CustomFieldSection
  • Type: Many-to-One
  • A custom field may belong to a custom field section.
• CustomField (1) --- (M) CustomFieldOption
  • Type: One-to-Many
  • Foreign Key in CustomFieldOption: custom_field_id references custom_fields.id
  • On Delete: Cascade (deleting a custom field deletes its options)
• CustomFieldValue (M) --- (1) CustomField
  • Type: Many-to-One
  • Foreign Key in CustomFieldValue: custom_field_id references custom_fields.id
  • On Delete: Cascade (deleting a custom field deletes its values)
• CustomFieldValue (M) --- (1) Entity (Polymorphic)
  • Type: Polymorphic Many-to-One
  • Polymorphic Fields: entity_type and entity_id
  • Associates custom field values with any entity in the application.

​

Constraints and Indexes

​

Unique Constraints

• CustomFieldSection
  • Unique Index: (entity_type, code, tenant_id) (if tenant_id is present)
• CustomField
  • Unique Index: (code, entity_type, tenant_id) (if tenant_id is present)
• CustomFieldOption
  • Unique Index: (custom_field_id, name, tenant_id) (if tenant_id is present)
• CustomFieldValue
  • Unique Index: (entity_type, entity_id, custom_field_id, tenant_id) (if tenant_id is present)

​

Foreign Keys and Indexes

• Foreign Keys
  • tenant_id
    • References tenants.id in all tables where multitenancy is enabled.
  • custom_field_section_id
    • References custom_field_sections.id in custom_fields.
  • custom_field_id
    • References custom_fields.id in custom_field_options and custom_field_values.
• Indexes
  • tenant_id
    • Indexed in all tables for query optimization in a multitenant environment.
  • Other Foreign Keys
    • Indexed to speed up joins and lookups.

​

Multitenancy Support

• Tenant Isolation
  • Data is scoped per tenant by including tenant_id in each table.
  • Unique constraints include tenant_id to prevent conflicts across tenants.
  • All queries should filter by tenant_id to ensure data isolation.
• Foreign Key Constraints
  • tenant_id references tenants.id (assuming a tenants table exists).
• Conditional Inclusion
  • The tenant_id column is added only if multitenancy is enabled (Utils::isTenantEnabled()).
  • This allows flexibility in deploying the application in both multitenant and single-tenant environments.
• Indexes
  • Indexes on tenant_id optimize tenant-specific queries.

​

Example Usage

​

Scenario: Storing Additional Customer Information

1. Define a Custom Field Section
   • CustomFieldSection with:
     • code: 'contact_details'
     • name: 'Contact Details'
     • type: 'additional'
     • entity_type: 'Customer'
     • sort_order: 1
     • description: 'Additional contact information for the customer.'
2. Define Custom Fields within the Section
   • CustomField for 'alternate_email':
     • custom_field_section_id: Reference to 'contact_details' section
     • code: 'alternate_email'
     • name: 'Alternate Email'
     • type: 'string'
     • entity_type: 'Customer'
     • validation_rules: {"email": true}
   • CustomField for 'preferred_contact_time':
     • custom_field_section_id: Reference to 'contact_details' section
     • code: 'preferred_contact_time'
     • name: 'Preferred Contact Time'
     • type: 'select'
     • entity_type: 'Customer'
     • Define options in CustomFieldOption:
       • Option 1:
         • custom_field_id: Reference to 'preferred_contact_time'
         • name: 'Morning'
         • sort_order: 1
       • Option 2:
         • custom_field_id: Reference to 'preferred_contact_time'
         • name: 'Afternoon'
         • sort_order: 2
       • Option 3:
         • custom_field_id: Reference to 'preferred_contact_time'
         • name: 'Evening'
         • sort_order: 3
3. Store Values for a Customer Entity
   • CustomFieldValue for 'alternate_email':
     • entity_type: 'Customer'
     • entity_id: 1001 (ID of the customer)
     • custom_field_id: Reference to 'alternate_email'
     • string_value: 'customer.alt@example.com'
   • CustomFieldValue for 'preferred_contact_time':
     • entity_type: 'Customer'
     • entity_id: 1001
     • custom_field_id: Reference to 'preferred_contact_time'
     • string_value: 'Afternoon'