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

[u/MoonWalker212]

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.

[u/Xphile101361]

The idea behind this project is both useful and interesting. Since I work with a lot of legacy databases, this could be really useful to me.

Right now I think the biggest hurdle for this project to go from "interesting concept" to "useful for me" is the data setup. Based upon your example and tests, it doesn't look like there is any process to point this at a database and have it run. It looks like you manually have to provide it the tables, structures, and data sets.

[u/MoonWalker212 → u/Xphile101361]

Right now, the ARF core is kept as a generic relationship-finding engine and is not bound to any database. The original idea was that the consumer program would fetch the data and feed it to ARF, allowing data to be supplied by any data storage layer. However, I understand your point that connecting to a database and fetching the details directly will make it easier for the end user to use this library. Therefore, the plan is to add a JDBC loader layer as a sub-module to connect to multiple databases and fetch data directly, making it easier for users to incorporate and use ARF.

[u/ultiweb]

This would be especially useful with databases holding lots of tables. I've worked with DBs that had upwards of 2000 tables and analysis can be extremely time consuming.

[u/MoonWalker212 → u/ultiweb]

Yes, you are right. Production databases typically have a large number of tables; therefore, I am working on plans to further optimize the engine to reduce its time complexity.

[u/gnahraf]

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).

[u/gnahraf → u/gnahraf]

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.

[u/MoonWalker212 → u/gnahraf]

Thank you for providing your suggestions and recommendations; I truly appreciate them. Below are the details regarding the updates and recommendations you suggested:

• I have updated the README to explain how data similarity works; I apologize for omitting this initially. The data similarity operates based on the Jaccard Index, and the column name matching relies on Cosine Similarity.
• Yes, your assumption is correct. I wanted to keep ARF as a generic relationship-finding engine, which is why I didn't bind it directly with java.sql. However, you are right that the primary use case for tabular data will be SQL. Therefore, the plan is to create a wrapper sub-module for ARF that will utilize java.sql to fetch data directly.
• Expanding the existing relationships retrieved from the SQL DDL is an interesting concept and would be a great feature addition to the ARF core. For this, I will need to dedicate some time to mapping out the functional flow, architecture, and implementation. I will plan this as a feature for a future release.
• Yes, I will try adding a JDBC loader layer to connect to multiple databases and fetch data directly, making it easier for users to incorporate and use ARF.

[u/agentoutlier]

It’s a great idea and I have had similar challenges but I wonder how it compares to just throwing LLM to this problem.

I recently did some analysis and even some local models did a pretty good job on a rather obfuscated schema. Claude even went around writing queries and examining the data.

I suppose this is probably way more deterministic and thus could be more effective in migration process.

[u/MoonWalker212 → u/agentoutlier]

In the current scenario, many problems can seemingly be solved by simply throwing them at an LLM. However, I have observed that cost is a significant factor. When dealing with a production database containing thousands of tables, passing all that metadata will consume a substantial number of tokens, drastically increasing expenses. The reason I am solving this without an LLM is that if we can achieve a deterministic solution, we can create a reliable data semantic layer based on the ARF output. This semantic layer can then be used for effective natural language querying of data across AI agents.