The database schema in EPESI seems complicated because each recordset has several tables. Some of them will not be very useful. Let me explain:
Below you see a list of tables that are used for one of the set of modules that we developed. You can see it is long and seems confusing.
The table name represents the name of the module. Here we have a module Premium called Checklist. It is located in directory /module/premium/checklist - therefore the name of the recordset is called premium_checklist_ and this recordset consists of a set of other recordests called Item, and each Item consists of Item Entries.
Now we have a list of:
premium_checklist_list
premium_checklist_item
premium_checklist_list entry
and so on. These are our recordsets and each of them have several tables:
_access
_access_clearance
_access_fields
_callback
_data_1
_edit_history
_edit_history_data
_favorite
_recent
_field
All these tables are needed for our CRUD engine Record Browser to work. Record Browser stores a lot of info about who and when modified a record, what fields where changed, by whom and when, stores info about access level, last visited records history, it needs to know who and when accessed what records and keep a list of 15 of them in the history per user... and a lot of other stuff. It is meaningful only when used by this engine. Raw data is stored in the table
_data_1
and probably this is what you would like to use.
The reason for my long introduction is that I am afraid that diagrams produced by any program will be unnecessarily complex, while the logic in EPESI is quite simple.
We have a user who produced such a db schema and posted it on our forum:
viewtopic.php?f=11&t=1348&hilit=diagram
but I and my team find it completely useless. A lot of tables store user preferences, positions of applets on the dashboard, preferences of each applet and so on. There is no point in showing them even.
You will find yourself at home just browsing tables. If you could filter out all other but _data_1 tables but I am afraid this is impossible. You just have to remember to link data between different recordset _data_1 table and link them by foreign key. We always link by record ID of the recordset. So if you want to join tables that should be easy. If I want to build a report showing how many sales opportunities an employee had and what was the value of sales just look into recordset premium_sales_opportunities_data_1 (for example) and crm_contacts. But you want to filter out just contacts from your company then join this table by id of your company, or rather filter it out based on employees who's company ID = your company ID. Don't forget to filter out active records only. We use lazy delete and never delete anything.
You will figure it out quickly. We have a plan to build a report wizard with graphs. You can do it already in EPESI but it requires coding unfortunately. I see a value in deploying 3rd party reporting and graphing tools to let the end user analyze data in many different ways but do it without writing a code. We are aware of that but I have no ETA on this feature. We are getting closer and closer to that. Soon you will be able for example import and export data to every recordset.
I hope I answered your questions.
