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

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 = '' AND
  REFERENCED_TABLE_NAME = '';

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 = '' AND
  REFERENCED_COLUMN_NAME = '';
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:

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

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 , or _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.


            





            answered Feb 22, 2017 at 14:42
        


        



    

        MawgMawg


            9,17555 gold badges4040 silver badges9191 bronze badges
        



            3