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?
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.
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)
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.
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.
If you have a bunch of .sql files, you can piece some open source tooling together to achieve what you want.
Look into DBML - "Database Markup Language". It is a platform-agnostic markup language for describing data models.
The first step is to transform your .sql file(s) to dbml.
The next step is to take your .dbml file(s) and generate svg for viewing.
