Re: ERD or datamodelling in a Peoplesoft - Oracle database

[David Thompson-Hall <david.thompson-hall@DOIT.WISC.EDU>]

At 10:11 AM 2/21/01 -0700, you wrote:
>
> I am currently looking for a way to reverse engineer from database to ERD  or
> produce data models from an Oracle database(with Peoplesoft objects). The
> version of Peoplesoft I'm using is HR 7.51 and SA 7.6.  I have tried the
> Peoplesoft website and the they do not have the most up to date ERDs. Is
> there a way a could generate these data models on the fly at any time so that
> the ERDs would be the latest and greatest for every release of Peoplesoft.


I'm afraid you won't be able to use a tool to get this information from PS
databases.

Since it is used on a wide variety of database platforms, PS has to build its
databases without using formally defined DB-level relational integrity.   It
would take an awful lot of work to write five or more parallel sets of table
definitions with relational key structures, etc. as defined by each different
database (Sybase, Oracle, Informix, etc.).  Plus, one of the DBs they support,
SQL Server, does not have relational integrity.

Instead, PS enforces relational integrity through code (or at least, that's the
idea; finding gaps and problems with this are a notorious part of working with
PS).

Because PS never uses the DB themselves to define relationships, any nice ERD
generation tool (like Visio, etc.) will not show up with any relations, because
they're just not there (on the formally defined DB level).

There is a place in the PS metadata tables where one can define parent-child
relationships between tables, but I don't know of anyone who's tried using
that, since it isn't always filled in; further, that won't tell you all you
really want to know for an ERD.

In short, sorry, no such luck.  It sure would be nice if PS could keep up their
own ERDs, but then, there's a whole lot of things that would be nice if PS did
them right...

DTH


David Thompson-Hall                                     1210 W. Dayton St.
Programmer/Analyst                                      Madison, WI 53706
Apps Tech/Business Operations Apps                      608/265-9571
Div of Info Technology (DoIT)
University of Wisconsin-Madison david.thompson-hall@doit.wisc.edu