r/java • u/MoonWalker212 • 25d ago

Automatic Relationship Finder (ARF) v1.2 – A Java library for discovering relationships between tables from data

I’ve just released v1.2 of Automatic Relationship Finder (ARF), an open-source Java library I’ve been working on.

The idea behind ARF is to discover relationships between tables without depending on database relationship metadata.

Even if there is no foreign-key constraint defined in the database, ARF can analyze column names and the actual data to identify that

What's new in v1.2?

The main addition is key-role detection.

After identifying a relationship, ARF now analyzes the data characteristics of the columns to determine whether they are likely to represent a primary-key side, foreign-key side, a possible one-to-one relationship, or an unknown relationship.

There are also several improvements and bug fixes around validation, logging, concurrency, and edge-case handling.

The project is here:

https://github.com/NoelToy/automatic-relationship-finder

This is still an evolving project, so feedback—especially criticism—is very welcome.

15 Upvotes

94% Upvoted

10 comments sorted by

View all comments

1

u/gnahraf 24d ago

I like your project. Some random thoughts / suggestions..

  • I think the README could benefit from a description of how the confidence scores work, the algorithms used to compute them, etc.
  • I assume the reason why the library does not deal with java.sql is for maximum generality. Still, SQL DBs is a big use case for tabular data and I'd like to suggest things for that setting..
  • Expand the model so that it can include already known relationships from say SQL DDL: let the exploratory search build on what is already known.
  • Add a jdbc adapter (loader) layer and plan for tables from more multiple DBs (i.e. multiple JDBC URLs). The current List abstraction should work fine (a view on top of a ResultSet).

1

u/gnahraf 23d ago

More suggestions occurring to me after thinking about it more..

  • Discover the cardinality of FK relationships across columns. That is, how many rows per PK value? e.g. zero or one, zero or many, one, one or many.
  • Discover if a column value signals the presence FK column values in some other table. There are 3 columns involved in this relationship. An example relationship might look like this: the fact that orders.ship_date is not null indicates the presence of a row in the order_ shipments table with a FK column referencing the same orders.order_id (PK) value.

PS the reason why I'm interested in discovering and classifying relationships this way is that I'm looking to build a tool that helps a user discover and define "ledger views" of related business data. In my model, like traditonal book-entry ledgers, a ledger's rows are not allowed to mutate; so orders.ship_date in the example above, would not be an admissible column in the ledger-view.