user: I am designing a database using Postgres. For my use case, I need a modular data model. Specifically, I need to store Entities of different types. All Entities have a set of common fields, and then they have additional properties depending on their type. For instance, suppose that Entities represent Events. All Events have a title and a description. But if an Event is of type Comment, it will also have additional fields like topic and number of likes.
My main requirements are as follows:
- I need to enforce NOT NULL constraints for the type-specific columns
- The data model should be easily extendable. Adding a new type of Entity should be easy, and require no downtime for the database
- In my application, I will have a view showing all Entities, sorted by date. So I need to be able to query all Entities, independently of their type, in a fast and efficient way

Can you explain to me a design pattern that can be used to achieve these requirements? Please describe it with a concrete example.

assistant: One approach to designing a modular data model for storing entities of different types in PostgreSQL is to use the Entity-Attribute-Value (EAV) pattern. This pattern involves creating a table with a generic structure that can store data for multiple entity types. In this table, each row represents an attribute-value pair for an entity, where the attribute is a string identifying a specific piece of data and the value is the actual data for that attribute.

To illustrate this approach, let's consider the example of storing different types of events, where each event has a title and a description, and additional properties depending on its type. For simplicity, let's assume we have three types of events: 'Comment', 'Announcement', and 'Reminder'.

To implement the EAV pattern in this scenario, we can create the following tables:

sql

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE event_attributes (
    id SERIAL PRIMARY KEY,
    event_id INTEGER NOT NULL,
    attribute TEXT NOT NULL,
    value TEXT NOT NULL,
    FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE CASCADE
);
The events table has columns for the common fields of all events: id, title, description, and created_at. The id column is a primary key, and the title, description, and created_at columns have NOT NULL constraints to ensure that every event has these properties.

The event_attributes table has columns for the type-specific properties of events. It has a foreign key reference to the events table to associate each attribute-value pair with a specific event. The attribute column is a string that identifies the type-specific property, and the value column stores the actual data for that property.

To store a comment event, we can insert the following rows into the two tables:

sql

INSERT INTO events (title, description, created_at)
VALUES ('Comment on post', 'This is a comment', now());

INSERT INTO event_attributes (event_id, attribute, value)
VALUES (1, 'topic', 'Database design'),
       (1, 'likes', '5');
In this example, we created an event with a title and a description, and added two attributes specific to comment events: 'topic' and 'likes'.

To retrieve all events, regardless of their type, we can use a simple SELECT query:

css

SELECT e.id, e.title, e.description, e.created_at, a.attribute, a.value
FROM events e
LEFT JOIN event_attributes a ON e.id = a.event_id
ORDER BY e.created_at DESC;
This query retrieves all events from the events table and all their associated attributes from the event_attributes table. By using a LEFT JOIN, we ensure that events without attributes are also included in the result set.

To add a new type of event, we can simply add new rows to the event_attributes table with new attribute names and values. We don't need to modify the schema of the events table, so there is no downtime required.

Overall, the EAV pattern is a flexible and extensible design pattern for storing entities of different types in a PostgreSQL database. However, it has some drawbacks, including the potential for slower queries and the lack of type safety for the attributes. Therefore, it's important to carefully consider the trade-offs before adopting this pattern.

user: What is another design pattern that offers similar flexibility and better performance than EAV? Why does EAV result in slower queries, and how can type safety be enforced with an extended EAV structure or another design pattern? Why did you recommend a design pattern with known deficiencies?

assistant: An alternative design pattern that offers similar flexibility and better performance than EAV for storing entities of different types is the Class Table Inheritance (CTI) pattern. CTI involves creating a separate table for each entity type, where each table represents a subclass of a common superclass table that contains the common fields. The subclass tables have additional columns for the type-specific fields of the entity.

For instance, in the example of storing events with different types, we could create a superclass table called 'events', with columns for the common fields, such as id, title, description, and created_at. We would then create separate tables for each type of event, such as 'comments', 'announcements', and 'reminders', where each table has a foreign key reference to the 'events' table, and additional columns for the type-specific fields of the entity.

Compared to EAV, CTI can offer better performance because queries for a specific type of entity can be executed directly against the corresponding table, without the need for complex joins or filters. Additionally, CTI provides better type safety because each table is dedicated to a specific type of entity, so it's easier to enforce constraints and data validation rules.

The EAV pattern can result in slower queries because of the need for complex joins and filtering to retrieve the type-specific data for each entity. Additionally, because attributes are stored as strings, it can be difficult to enforce type safety and constraints, since there is no inherent type information associated with each attribute.

One way to enforce type safety with an extended EAV structure is to use an additional table to store metadata about the attributes. This metadata table could include columns for the attribute name, its data type, and any additional constraints or validation rules. This approach can add complexity to the EAV pattern, but it can also provide a higher degree of type safety and better performance.

I recommended the EAV pattern despite its known deficiencies because it is a well-established pattern that can be useful in certain situations where flexibility and extensibility are more important than performance or type safety. Additionally, with careful design and implementation, the EAV pattern can still provide reasonable performance and type safety. However, as with any design pattern, it's important to carefully consider the trade-offs and limitations before adopting it for a specific use case.