Is there any free data modeling software that exports the schema to a machine readable format other than SQL?

Question

I am in the process of mapping my country's open data, and the relationships between them, and I need to do it in both human and machine readable formats.

I am looking at diagramming tools for the human readable part, but I would need the tool to be able to export the results to a machine readable format other than SQL, such as json or xml, so as to be able to pass it to a program of my own writing for later processing.

Since I would not need to be dependent on SQL, I would like as many of the UML relationship types to be supported. However, I would be able to make do with association, reflective association (self reference) and multiplicity.

Is there any free tool that can achieve this?

P.S.: If not, are there any Python or C# libraries that can read SQL DDL statements into a class structure?

Answer 1

I'm still a little unclear. Couldn't you use Python to query the database and use that result to generate XML?

Ah, " my country's open data, and the relationships between them". So, not necessarily every piece of data, but the relationships between tables, for instance?

If you have access to the meta data (schema). It you do, this shouldn't be difficult. If you don’t, can you sue SQL queries, or are you limited to just getting data in a format that they (some government website) want to offer you?

To clarify: to determine relationships among data, your best approach is to look for foreign keys.

You can get this rom the database schema. Or, for instance, by executing the MySql SHOW CREATE TABLE command.

This excellent answer shows

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Of course, it is probably the case that you don’t have such direct access to the actual database, and just an API which does the querying for you.

In that case, I see two possibilities:

1. Contact whoever supplies the data. If they are making it freely available then they are probably going to be not only willing, but actually pleased, to help you out. If you are really lucky, you will get full read-only access and can use tools like MySql Workbench which can help you reverse engineer an existing database and visualize it like this

2. Dump every table. It won’t be presented that way, but you are probably getting the result of `SELECT * From ’ and look, by hand or by code, for identically named columns.

Good database design will see meaningfully named columns with the same column name used in several columns. E.g. customer_id, order_id, etc

And, with good database design/good luck, they will be of the form <table_name¬_Id, or <table_name>_index, or similar.

From which you can surmise that customer_id is the PRIMARY KEY in the customers table, and uis used as a FOREIGN KEY in the orders table, thus estcablishing your relationship.