Understanding ER Tables and Their Implementation in DBLE

In the previous installment we introduced the use of global tables; now we turn to ER tables and how to implement them in DBLE.

ER Table

ER tables represent logical foreign‑key relationships between tables, such as the sales and sales_detail tables shown on the right side of the diagram. Tables with foreign‑key relationships can be split using those keys or depend on the foreign‑key node.

For example, when column A has a 1:N relationship with a foreign‑key column, the foreign key uniquely determines its routing. Middleware can split on column A and, because of the foreign‑key relationship, join the related data in another table, allowing the whole query to be dispatched without a second lookup.

Consider the configuration shown in the diagram: it is a typical ER‑table setup with a parent and one or more child tables. Children can themselves have children, theoretically allowing unlimited nesting, though more than three levels are not recommended.

The first two rows illustrate the most common split method. The second row ( childTable ) includes a joinKey that links its column to the parent’s column. In other words, child1_id in the child table is a foreign key referencing id in tb_parent . Knowing this relationship enables the query to be sent directly to the database.

Implementation Method 1

We demonstrate with an example. Using the query select * from tb_parent we inspect the data in the tables. Test data has been pre‑inserted, and the two tables contain essentially the same rows. We then perform a JOIN between the tables and examine the result and the execution plan using EXPLAIN \G .

The plan shows four rows: after the two SQL statements are dispatched as a whole, the results are simply merged, reflecting the join key execution plan. If we change the JOIN column to id instead of the previous column, the query falls back to a cross‑database approach, requiring the middleware to compute the ON condition internally. The plan shows an ORDER BY, followed by a merge, and finally the middleware performs the JOIN.

Conclusion

Using ER tables is beneficial only when the defined foreign‑key relationships align with your business logic, allowing the optimizer to push the entire SQL down to the database. Users should analyze whether their use case fits this pattern before adopting ER tables.

For further reference, the DBLE project and related repositories are available at:

https://github.com/actiontech/dble

https://github.com/actiontech/dble-docs-cn

https://github.com/actiontech/dble-test-suite

Course inquiries can be directed to the ActiontechOSS WeChat public account or the official technical group (ID 669663113).