Software to create a database structure from a batch of XSD files

Question

In my work we're now generating a database from a lot of XSD (50-100) for generating reports. This is a constriction imposed by the ministery, so there's nothing we can do. So the problem I'm having is that doing them by hand with no errors is almost imposible, lots of missing foreing keys and isolated tables, so I'm looking for a software that can receive the whole batch of XSD and generates the whole structure of the database. I have checkeed XSD2DB, which can't connect to my, and Altova's XMLSpy, which is expensive and doesn't solve my issue because I forced to load each XSD separately and without keys relating to other tables.

The best case scenario requirements would be:

• Generate a DB structure given a batch of XSD
• Connect to MySQL
• Open source > Free > Paid
• Be python-friendly
• Be able to graphically see the generated DB structure and manipulate it

The important one is the first one, please and thank you!

Answer 1

You're asking a lot here, and I don't think you'll find anything off-the-shelf.

You haven't said how the various XSD schema documents are related, e.g. whether there is duplication across them, whether they link to each other, whether they are even consistent.

Typically XSD documents describe message formats, and a message format is not a data model. For example there might be messages associated with creating a new customer account or changing a customer address, and deriving a data model from these ("customers have multiple addresses") involves a process of synthesis that can't be fully automated. That's especially true, of course, if the different schema documents use inconsistent naming or inconsistent data types for the same entities and properties. The task of synthesizing the data model, and the extent to which software tools can help the process, will depend very much on the level of consistency that you find.

You also haven't said what kind of database you are implementing but I assume it's a relational database (rather than, say, an XML database). That suggests you've really got a 2-stage process here: first, synthesise a data model by bottom-up analysis of the XSD messages; second, do the usual normalization processes to construct a relational schema from the conceptual data model. And presumably at the end you want some kind of mapping from messages to tables to emerge.