Reverse Engineer OJS Database with Relationships

[amecily]

Hi there,

I have been trying to create an ERD of the OJS database from a .SQL file in order to document the database design to make it easier for customisation etc. I have managed to reverse engineer the file using MySQL WorkBench, and Toad Data Modeler, and I've tried a number of others. But the relationships between the entities do not form, so the diagram is not of much help.

Is there a way to reverse engineer the database so that the relationships are developed as well?

Thanks a lot,
Amy

[asmecher]

Hi Amy,

The problem is that we're using ADODB as a database abstraction layer in order to support several DBMSs (MySQL and PostgreSQL); ADODB doesn't explicitly define foreign keys, so you won't be able to get them back from the database either (unless the tool you're using makes guesses based on e.g. column names).

I've taken a crack a couple of times at making a diagram, but there are so many tables that it's really hard to lay everything out usefully. Internally we generally make use of the XML descriptor when we want to look up the database structure (e.g. dbscripts/xml/ojs_schema.xml). Is there a particular relationship I could describe for you?

Regards,
Alec Smecher
Public Knowledge Project Team

[amecily]

Hi Alec,

Thanks a lot for the response.

Okay, I understand what you mean. No thanks regarding the specific relationship at the moment - might need to know some relationships later on, but for now it's just documentation.

Just two questions: Do you know of any tools that do make assumptions based on column names etc.?
And is there no way to generate a diagram using the XML document?

Thanks,
Amy

[asmecher]

Hi Amy,

Unfortunately I don't have any recommendations for you. If you have (or anyone else has), please let us know; we would love to publish diagrams for the database but it's been too much of a pain with the manual tools I've tried.

Regards,
Alec Smecher
Public Knowledge Project Team

[amecily]

Hi Alec,

I found a tool in Toad Data Modeler that infers relationships based on field numbers and other criteria (I assume). I have managed to generate the relationships between entities. It is a huge diagram though! Would you like me to send you the diagram?

For anyone who is looking for this answer, in Toad Data Modeler (after reverse engineering the database), click on Macros in the Menu bar, then All -> Infer Relationship.

Thanks,
Amy

[asmecher]

Hi Amy,

Please do -- I used Toad many years ago and found it useful, but unfortunately I'm using a Linux platform now. If the diagram looks promising I'll see if someone on our team can investigate it a little further. It might help to try generating the diagram using subsets of the full table list -- the editorial process, for example, or subscriptions, or settings. A single diagram with all the tables will be enormous.

Regards,
Alec Smecher
Public Knowledge Project Team

[amecily]

Hi Alec,

Yes, all these tables do make the diagram enormous. Do perhaps have a list of the main tables that should be used in the diagram? That would be very helpful so that I could select only those tables. Then I'll make the diagram and should I place it on the forum or email it to you?

Thanks,
Amy

[asmecher]

Hi Amy,

I've just sent you a PM.

Regards,
Alec Smecher
Public Knowledge Project Team