0

Ok, let me start off by stating I have search google and just about every resource I can think of, but it is possible I am not asking the question correctly. If not, please feel free to correct that question.

What I am trying to do is create a beast of a relational data diagram for a series of tables under multiple schema for my companies many oracle/Access/SQLServer databases. Note, these databases already exists, and this is an oil and gas company so we have several different databases, schema, and tables. We report using Spotfire, so we can easily link different datasets from various sources, as long as we have relationships in place.

As you can imagine, this can be a nightmare trying to figure out how these all relate to one another, especially when many of these tables were slapped together without much structure. I want to be able to visualize the connections between the various tables that I will be doing reporting on, and figure an ER like diagram would be the best start. I am having a lot of trouble with the "modeling" tools I have at my disposal.

I started with Visio 2016, but this is far too cumbersome and the design options for the database diagrams is very limited. I understand that I can possibly import my data tables via excel, but that still doesn't get past the very limited design options in Visio. Note, I have about 100 tables to work through, and some of these have up to 200 columns.

I then went to excel and thought, maybe I can use VBA to just link shapes and auto generate my "prettier" Entities and then I can manually link them as I see fit. However, I cannot get generic shapes to link in an "ER Diagram" entity style and the smartart options in office are not suited for this.

Does anyone know of a potentially better means to accomplish this with Office tools? Or would I really have to try and push my company to invest in a different software? At the moment my tools are: Office 2016 (all), SQL Developer, SQLServer Management Studio, Spotfire (who knows that software is amazing).

TL/DR: I need to create a relational data model for about close to a hundred tables, across multiple schema/databases, with as many as 200 columns per table, and am looking for a tool that will let me do this efficiently.

Chad Holland
  • 1
  • 2

1 Answers1

0

If you mean "I want to design a single data model that incorporates all of the database schema across multiple databases." Then you can do this with a free tool called NORMA.

NORMA can read the schema of your databases and convert them into a set of object-role models that are "database neutral"

You can then use NORMA to merge the models into a single model. (The techniques are described in the tutorials)

When you have finished, you can view your "beast" object-role model as a single logical/relational model.

Then you can generate the DDL for your preferred DBMS (Oracle, SQL Server etc).

NORMA is an extension to Visual Studio. You can download the tutorials from here.

NORMA implements the object-role modeling method as defined by Dr Terry Halpin.

Ken Evans
  • 362
  • 1
  • 11