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?
5 Answers 5
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.Michael– Michael2013年11月20日 13:30:03 +00:00Commented Nov 20, 2013 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– Marian2013年11月20日 14:10:38 +00:00Commented Nov 20, 2013 at 14:10
-
1I think I will have to do it manually. Thank you for your help though.Michael– Michael2013年11月20日 15:15:16 +00:00Commented Nov 20, 2013 at 15:15
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.
Explore related questions
See similar questions with these tags.