The ER diagram related to LiveCycle ES is something, that is Adobe Specific and any changes to its dependencies is never advisable and neither is supported. However, related to the LC ES ER diagram, even if one can develop the ER using various tools available in the Open Source market, it is difficult to understand the complete architecture of it, without complete knowledge of the architecture and its dependencies, constraints.
If you would be more specific in telling about which table constraints or which module specific you want to analyze then it would be better to pick up only that section of the DB ER for analysis.
Please find attached couple of screenshots that I have related to the LC ES DB ER diagrams.
Let me know if it helps.
I've detailed understanding of how LC works so I wasn't trying to find out the complete architecture of it from the database point of view.
The intension is to get the relationship information in the ER diagram so I can send it to other guys to get some reports generated on LC data. I get your point that Adobe can change the database schema anytime with patches or new version of LC but without this complete ER diagram the team working on LC solution isn't scaling properly.
I've tried visio to generate the diagram but it's not as good as some other tools (may be)...can everyone share what they have used to generate ER diagram by reverse engineering the database?
Try using the MySQL Workbench 5.2 for taking the ER. This would give a clarity for the ER for the reports, as you mentioned.
I use the same and the last screenshots attached gave a clarity on the ER, however, it would be complex, but since, you are already aware of the underlying dependencies and constraints it would be easier for you.
Hope it helps..
I'll give it a try.