Dynamic Entity Attribute Value Model

Creating a database model that supports dynamic entity creation requires a flexible and scalable design. The goal is to design tables that can accommodate the creation of new entities  on the fly, without the need for structural database changes. 

Applications  built on a microservices architecture, where each microservice manages its own database. This architecture enhances service independence and scalability. Several of these microservices also implement the dynamic entity model in their databases, offering flexibility for managing dynamic and diverse data types and relationships.

Proposed dynamic entity design allows for individual microservices to be updated, scaled, and maintained independently, promoting agility and robustness in our system's overall functionality.

This model is Ideal for scenarios where entity attributes are numerous and varied, and where new attributes might be frequently added

Components:

1. BaseEntity: Represents generic entity types in our system.

                Example ; Study, Parcel, Sample etc

     2. EntityInstance: Each instance of an entity is recorded here, storing unique occurrences of BaseEntity types. 

                Example, a specific study or a particular task.

     3. EntityAttribute: Defines the set of attributes applicable to each entity type.

•         Attribute can contain validations like Minlength, max length, validations etc
• -metadata for attributes added to attributes table

     4. EntityAttributeValue: Stores values for these attributes for each entity instance.  

     

     5. EntityMetadata: Provides additional descriptive information or configuration settings for each entity type or instance, enhancing the contextual understanding of the entities. 

    6. EntityRelationship: Manages the relationships between different entities, crucial for representing complex associations such as the linkage between studies and samples or tasks and their parent entities.

• The EntityRelationship table essentially serves as a cross-reference (XRef) table, especially in a many-to-many relationship scenario. 
• EntityInstance records are joined through EntityRelationship to find all related child entities for a given parent entity.

Managing Dynamic Attributes

Key Challenges

• Attributes differ significantly across entity types and instances, requiring a system that can accommodate a wide range of data structure
• As the number of entities and attributes grows, the system must scale efficiently without compromising performance.
• Ensuring accuracy and consistency of data across various entities with diverse attributes.

Strategies for Managing Dynamic Attributes

• DB model allows our microservices to define and modify attributes without restructuring the database schema.
• Attribute Metadata Management: Utilize EntityMetadata to store additional information about attributes, such as data validation rules, which helps in maintaining data quality and integrity.
• APIs will be designed to dynamically generate responses based on the attributes of the requested entities, ensuring flexibility and relevance in data delivery.

Use cases

• When a new attribute is introduced, it will be registered in the EntityAttribute table, making it immediately available for association with entity instances.
• Values for these attributes are stored in the EntityAttributeValue table, allowing for efficient retrieval and manipulation as per business logic requirements.
• The frontend dynamically adjusts to display and interact with these attributes, providing a seamless user experience regardless of the underlying data 

Design Considerations

UI Structure  

• Generic page that can adapt to display any entity type. This is already in place
• Dynamic form to render different types of inputs/display fields based on the data type of each attribute ( text fields, data pickers, dropdowns etc)
• metadata can be used to add additional information or influence the rendering of the entity (e.g., adding tooltips, conditional formatting).
• UI to call  endpoints passing relevant entity and instance ID’s

Fetching Data

• API endpoints to fetch data for a specific entity including its attributes and metadata

Example 

• To fetch a specific Study instance, Query Should join baseentity, EntityInstance, EntityAttribute and EntityAttributeValue tables
• If these  tables have large number of rows, Performance will be extremely slow. To mitigate that we can consider indexes on frequently queried columns like entityid

Data Integrity and Consistency –  

• Enforce data type consistency. For instance, if an attribute 'StartDate' is designed to store dates, the schema restricts this field to date data types only.
• Data Validation :before inserting or updating data, the application layer checks if a 'StartDate' is indeed a valid date and not just a random string or number.
• When a new 'Study' entity is created, and multiple attributes are added, this process is wrapped in a transaction. If adding any attribute fails, the entire operation is rolled back to avoid partial updates.

Optimization and Performance:

• Leveraging indexing on the EntityAttributeValue table for faster query execution.
• Dynamic model queries can be complex and might impact performance. We should consider query optimization techniques, caching, or even indexed views if the performance becomes a concern.