Implement Relation and Rollup column for Database Tables

[domenico-ruggiano]

Hello team,

thank you for creating AFFiNE, I'm loving this tool for organizing and collaborating in my organization. I’d like to propose adding Relation and Rollup columns, which would allow users to create connections between data in multiple tables and leverage those relationships for deeper insights.

Relation Columns

Relation columns allow users to link entries between tables.

• Example: Connect a “Customers” table with an “Orders” table to see which customers placed which orders.
• Two-way relations: Automatically update both sides of the connection. For example, if a customer is added to an order in the “Orders” table, the order will appear in the “Customers” table.
• Self-referencing relations: Allow a table to reference its own entries, such as creating parent-child relationships within a “Tasks” table to track dependencies.

Rollup Columns

Rollup columns aggregate data from related tables based on relations.

• Example: In the “Customers” table, calculate the total amount spent by each customer based on data from the related “Orders” table.
• Aggregation options: Include sum, average, count, min, max, earliest date, latest date, and more.

Use Cases

1. Project Management: Link a “Tasks” table to a “Projects” table, allowing teams to see task breakdowns and project progress.
2. CRM: Connect a “Clients” table to an “Interactions” table to track client activity and engagement.
3. Inventory Management: Link a “Products” table to a “Sales” table to track stock levels and revenue.
4. Task Hierarchies: Use self-referencing relations in a “Tasks” table to create parent-child task relationships and track dependencies.

Benefits to Users

• Streamlined Data Organization: Relating tables reduces duplication and improves efficiency.
• Dynamic Data Insights: Rollups provide real-time summaries and calculations for better decision-making.
• Expanded Use Cases: Makes AFFiNE applicable to complex workflows, including project management, CRM, and inventory tracking.

I’d love to hear your thoughts and collaborate on refining this idea further.