3

I have a database with 193 tables and I need to create an entity-relationship diagram from them. There are no formal relationships between the tables with respect to FKs. The relationships are mapped with 1-* tables.

The database in question is the MusicBrainz database found here.

SchemaSpy doesn't detect the relationships.

Other than myself creating it manually does anyone have any suggestions. Or is anyone familiar with this database that could offer some assistance?

6

The relationship between tables in any relational database is done using Foreign Keys.

From the Wikipedia definition:

Foreign keys play an essential role in database design. One important part of database design is making sure that relationships between real-world entities are reflected in the database by references, using foreign keys to refer from one table to another.

Without FKs there will be no way for any schema modelling tool of knowing that two tables are related.

A solution might be for you to script out FKs by parsing the names of the tables (if the related tables have similar names), then names and data types of columns that define the relationship. But that's no trivial task.

  • the mapping tables take the form l_artist_record joining the tables artist and record through entity0 and entity1. I was hoping there would be an easy way. – Mike Nov 20 '13 at 13:30
  • Wow, not even the naming convention can help. Sorry, except for manual scripting and matching of the needed columns, I don't think any ER tool can scan and display properly that database. – Marian Nov 20 '13 at 14:10
  • 1
    I think I will have to do it manually. Thank you for your help though. – Mike Nov 20 '13 at 15:15
3

It may be more trouble than its worth but there is a paper on reverse engineering relational database schemas: Clustering relations into abstract ER schemas for database reverse engineering

This paper describes algorithms for inferring relations in the absence of foreign keys. I have implemented parts of this as part of a school assignment with some degree of success.

2

There is a package in Debian / Ubuntu family named postgresql-autodoc. Easy to use and generate diagrams in a plethora of formats (.dia, .html, .neato, .dot, .xml)

1

I have a solution for you - Dataedo tool. It reads database schema like other tools, but it's power is you can define missing FKs in the documentation itself (without interfering with database). Then you can create ER diagram from that model. It lets you describe tables and columns (create Data Dictionary) and export it as a complete documentation to PDF and HTML.

I'm the product manager of Dataedo.

-1

xplore.io will generate a schema from your database with logical relations (not only FK constraints) you can also add manual relations as you wish.

  • Hi there welcome to the site. I looked at their website and it's not really clear how the tool would help. Could you elaborate a bit more? Oh, and if you're affiliated please disclose. – Tom V Dec 11 '16 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.