Entity-Attribute-Value (EAV)
This is a design pattern where each entity is represented by a set of attribute-value pairs, instead of having a fixed schema with predefined columns. The relational databases are based on a rigid structure that requires defining the attributes and data types of each entity beforehand. However, EAV is a technique used to represent entities with dynamic and variable attributes in a flexible way by using three tables: one for entities, one for attributes, and one for values.
For example
Suppose we have a table called products that stores information about products, and we want to store different attributes for different types of products, such as color, size, weight, etc. We can use EAV to store all the products and their attributes in three tables, with foreign keys that link them together. The tables might look like this:
Advantages of the Entity-Attribute-Value
- It provides a flexible and dynamic schema that can accommodate any number and type of attributes for each entity.
- It allows adding new attributes easily by inserting new rows in the attribute table.
- It supports sparse data by storing only the relevant attributes for each entity.
Disadvantages of the Entity-Attribute-Value
- It violates the normal form and the relational model by storing data in a non-tabular format.
- It complicates data access and manipulation by requiring complex queries and joins between tables.
- It reduces performance and scalability by increasing the size and number of tables and indexes.
- It makes data validation and integrity difficult by storing values as strings without data types or constraints.
Note: EAV is suitable for scenarios where the entities have unpredictable and heterogeneous attributes that change frequently.
EAV diagram:
Below is the explanation of the above diagram:
- The image shows three tables: issue, customfieldvalue, and customfield. Each table has a primary key, which is a field that uniquely identifies each record in the table.
- The primary key is shown with an underline in the image. The tables also have foreign keys, which are fields that reference the primary key of another table. The foreign keys are shown with an arrow in the image.
- The issue table has two fields: ID and decimal(18,0). The ID field is the primary key of the table, and it stores a unique number for each issue. The decimal(18,0) field stores the ID of the custom field that is associated with the issue.
- The customfieldvalue table has six fields: ID, decimal(18,0), CUSTOMFIELD, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, and VALUETYPE. The ID field is the primary key of the table, and it stores a unique number for each custom field value.
- The decimal(18,0) field stores the ID of the issue that has the custom field value. The CUSTOMFIELD field stores the ID of the custom field that defines the custom field value. The PARENTKEY field stores the ID of the parent custom field value, if any.
- The STRINGVALUE, NUMBERVALUE, and TEXTVALUE fields store the actual value of the custom field value, depending on its data type. The VALUETYPE field stores the data type of the custom field value, such as string, number, or text.
- The customfield table has two fields: ID and decimal(18,0). The ID field is the primary key of the table, and it stores a unique number for each custom field. The decimal(18,0) field stores the name of the custom field.
The image shows how the tables are related to each other with arrows. The arrows indicate that there is a one-to-many relationship between the issue and customfieldvalue tables, and between the customfield and customfieldvalue tables. This means that each record in the issue or customfield table can have multiple records in the customfieldvalue table, but each record in the customfieldvalue table can have only one record in the issue or customfield table.
For example, if there is a record for issue 1 in the issue table, there can be multiple records for issue 1 in the customfieldvalue table with different values for different custom fields.
Design Patterns for Relational Databases
Relational databases are a way of storing and managing data in software development. They help you keep your data in order and find it quickly. But to use relational databases well, you need to follow some patterns that solve common problems and make your data work better. In this article, we will look at different patterns for relational databases, and explain how they can help you with specific issues and improve your database performance.
Important Topics for the Design Patterns for Relational Databases
- What are relational databases?
- Design Patterns for Relational Databases
- 1. Single Table Inheritance (STI)
- 2. Class Table Inheritance (CTI)
- 3. Entity-Attribute-Value (EAV)
- 4. Composite Key
- 5. Multipart Index
- 6. Materialized View
- 7. Many-to-Many Relationship
- 8. Caching
- 10. Queueing
- 11. Audit Log
- 12. Versioning