ER Diagram for ERPNEXT or Data Dictionary to assist in understanding and contributing to development

Hello Guys,

I see a great diagram here http://erpnext.com/files/erpnext_links.html
https://frappe.io/files/erpnext_links.html
and it is pretty and the relationships are layed out in the code:

eg:
// define relationships - focus on “source” and “target”
// “source_type” and “target_type” is extra data used to selectively
truncate data set
var _data = [
{“source”: “Account”, “target_type”: “Master”, “target”: “Cost
Center”, “source_type”: “Master”},
{“source”: “Account”, “target_type”: null, “target”: “GL Entry”,
“source_type”: “Master”},
{“source”: “Account”, “target_type”: null, “target”: “Journal
Voucher”, “source_type”: “Master”},
{“source”: “Account”, “target_type”: “Master”, “target”: “Mode of
Payment”, “source_type”: “Master”},

Is there a data dictionary in the database somewhere that describes
the relationships between database tables and describing relation type
eg: 1 to many; many to many etc…

In addition are there any diagrams showing the tables layed out using
a err diagramming tool like “mysql-workbench”

I would like to contribute to the project some of the systems we have
developed for our internal accounting over the years and teh ER
Diagram is
absolutely necessary to be an effective contributor.

Thanks.


You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.
To post to this group, send email to er...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Hi Erran,

Unfortunately no ER diagram yet. But you can use MySQL workbench and other such schema browsing tools to get started.

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 08-May-2013, at 5:16 PM, Erran Botha <er...@gmail.com> wrote:

Hello Guys,

I see a great diagram here http://erpnext.com/files/erpnext_links.html
and it is pretty and the relationships are layed out in the code:

eg:
// define relationships - focus on "source" and "target"
// "source_type" and "target_type" is extra data used to selectively
truncate data set
var _data = [
{"source": "Account", "target_type": "Master", "target": "Cost
Center", "source_type": "Master"},
{"source": "Account", "target_type": null, "target": "GL Entry",
"source_type": "Master"},
{"source": "Account", "target_type": null, "target": "Journal
Voucher", "source_type": "Master"},
{"source": "Account", "target_type": "Master", "target": "Mode of
Payment", "source_type": "Master"},

Is there a data dictionary in the database somewhere that describes
the relationships between database tables and describing relation type
eg: 1 to many; many to many etc...

In addition are there any diagrams showing the tables layed out using
a err diagramming tool like "mysql-workbench"

I would like to contribute to the project some of the systems we have
developed for our internal accounting over the years and teh ER
Diagram is
absolutely necessary to be an effective contributor.

Thanks.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.
To post to this group, send email to er...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.





You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

You must at least have a data dictionary besides the relationships in the sample source code?

On Wednesday, May 8, 2013 9:53:56 PM UTC+10, rushabh wrote:

Hi Erran,

Unfortunately no ER diagram yet. But you can use MySQL workbench and other such schema browsing tools to get started.

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 08-May-2013, at 5:16 PM, Erran Botha <er...@gmail.com> wrote:

Hello Guys,

I see a great diagram here http://erpnext.com/files/erpnext_links.html
and it is pretty and the relationships are layed out in the code:

eg:
// define relationships - focus on "source" and "target"
// "source_type" and "target_type" is extra data used to selectively
truncate data set
var _data = [
{"source": "Account", "target_type": "Master", "target": "Cost
Center", "source_type": "Master"},
{"source": "Account", "target_type": null, "target": "GL Entry",
"source_type": "Master"},
{"source": "Account", "target_type": null, "target": "Journal
Voucher", "source_type": "Master"},
{"source": "Account", "target_type": "Master", "target": "Mode of
Payment", "source_type": "Master"},

Is there a data dictionary in the database somewhere that describes
the relationships between database tables and describing relation type
eg: 1 to many; many to many etc...

In addition are there any diagrams showing the tables layed out using
a err diagramming tool like "mysql-workbench"

I would like to contribute to the project some of the systems we have
developed for our internal accounting over the years and teh ER
Diagram is
absolutely necessary to be an effective contributor.

Thanks.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.
To post to this group, send email to erpnext-dev...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.





You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/-soa-1wCVFkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Erran,

The table relationships are exactly same as DocTypes. Every time a DocType is saved, the schema is updated. You can get a better idea by browsing DocTypes. If you are logged in as Administrator, go to #List/DocType in the url

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 09-May-2013, at 5:38 AM, Erran Botha <er...@gmail.com> wrote:

You must at least have a data dictionary besides the relationships in the sample source code?

On Wednesday, May 8, 2013 9:53:56 PM UTC+10, rushabh wrote:
Hi Erran,

Unfortunately no ER diagram yet. But you can use MySQL workbench and other such schema browsing tools to get started.

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 08-May-2013, at 5:16 PM, Erran Botha <er...@gmail.com> wrote:

Hello Guys,

I see a great diagram here http://erpnext.com/files/erpnext_links.html
and it is pretty and the relationships are layed out in the code:

eg:
// define relationships - focus on "source" and "target"
// "source_type" and "target_type" is extra data used to selectively
truncate data set
var _data = [
{"source": "Account", "target_type": "Master", "target": "Cost
Center", "source_type": "Master"},
{"source": "Account", "target_type": null, "target": "GL Entry",
"source_type": "Master"},
{"source": "Account", "target_type": null, "target": "Journal
Voucher", "source_type": "Master"},
{"source": "Account", "target_type": "Master", "target": "Mode of
Payment", "source_type": "Master"},

Is there a data dictionary in the database somewhere that describes
the relationships between database tables and describing relation type
eg: 1 to many; many to many etc...

In addition are there any diagrams showing the tables layed out using
a err diagramming tool like "mysql-workbench"

I would like to contribute to the project some of the systems we have
developed for our internal accounting over the years and teh ER
Diagram is
absolutely necessary to be an effective contributor.

Thanks.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.
To post to this group, send email to erpnext-dev...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.






You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To post to this group, send email to er…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/-soa-1wCVFkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Thanks Rushabh,

That worked.

If the database tables are created by the Doc Types definition then where are the doc type definitions stored? In the database?
Where exactly are the primary key and foreign keys defined for each doc/table?

Why do some tables not have a primary key of a unique “id” but appear to use the name/description as the key to join to other tables eg contact.name etc…
This seems to create a problem with updating the name and having to deal with cascading updates to other tables violating basic database normalization standards.

Have I missed something basic here?

On Thursday, May 9, 2013 12:53:21 PM UTC+10, rushabh wrote:

Erran,

The table relationships are exactly same as DocTypes. Every time a DocType is saved, the schema is updated. You can get a better idea by browsing DocTypes. If you are logged in as Administrator, go to #List/DocType in the url

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 09-May-2013, at 5:38 AM, Erran Botha <er...@gmail.com> wrote:

You must at least have a data dictionary besides the relationships in the sample source code?

On Wednesday, May 8, 2013 9:53:56 PM UTC+10, rushabh wrote:
Hi Erran,

Unfortunately no ER diagram yet. But you can use MySQL workbench and other such schema browsing tools to get started.

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 08-May-2013, at 5:16 PM, Erran Botha <er...@gmail.com> wrote:

Hello Guys,

I see a great diagram here http://erpnext.com/files/erpnext_links.html
and it is pretty and the relationships are layed out in the code:

eg:
// define relationships - focus on "source" and "target"
// "source_type" and "target_type" is extra data used to selectively
truncate data set
var _data = [
{"source": "Account", "target_type": "Master", "target": "Cost
Center", "source_type": "Master"},
{"source": "Account", "target_type": null, "target": "GL Entry",
"source_type": "Master"},
{"source": "Account", "target_type": null, "target": "Journal
Voucher", "source_type": "Master"},
{"source": "Account", "target_type": "Master", "target": "Mode of
Payment", "source_type": "Master"},

Is there a data dictionary in the database somewhere that describes
the relationships between database tables and describing relation type
eg: 1 to many; many to many etc...

In addition are there any diagrams showing the tables layed out using
a err diagramming tool like "mysql-workbench"

I would like to contribute to the project some of the systems we have
developed for our internal accounting over the years and teh ER
Diagram is
absolutely necessary to be an effective contributor.

Thanks.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.
To post to this group, send email to er...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.






You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/-soa-1wCVFkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/aef94f42-2e41-4732-990f-7f1d5a955eb1%40googlegroups.com?hl=en.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Erran,

Replies below:


On Sat, May 11, 2013 at 3:52 PM, Erran Botha <er...@gmail.com> wrote:
Thanks Rushabh,

That worked.

If the database tables are created by the Doc Types definition then where are the doc type definitions stored? In the database?

In ".txt" files -- for example app/accounts/doctype/account/account.txt - whenever you update the system using "lib/wnf.py --update" all schema is automatically updated
Where exactly are the primary key and foreign keys defined for each doc/table?

"Link" fields are foreign keys.

Why do some tables not have a primary key of a unique "id" but appear to use the name/description as the key to join to other tables eg contact.name etc..

All tables have a unique "name" (which is actually id). This could be a numeric series or name (based on user preferences in masters like Customer, Supplier, Item)
This seems to create a problem with updating the name and having to deal with cascading updates to other tables violating basic database normalization standards.

We have a slightly custom designs for this. For cascading updates, you have to use the API - all the triggers are written in Python files in the DocType folders (see the account folder)

Have I missed something basic here?


On Thursday, May 9, 2013 12:53:21 PM UTC+10, rushabh wrote:
Erran,

The table relationships are exactly same as DocTypes. Every time a DocType is saved, the schema is updated. You can get a better idea by browsing DocTypes. If you are logged in as Administrator, go to #List/DocType in the url

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 09-May-2013, at 5:38 AM, Erran Botha <er...@gmail.com> wrote:

You must at least have a data dictionary besides the relationships in the sample source code?

On Wednesday, May 8, 2013 9:53:56 PM UTC+10, rushabh wrote:
Hi Erran,

Unfortunately no ER diagram yet. But you can use MySQL workbench and other such schema browsing tools to get started.

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 08-May-2013, at 5:16 PM, Erran Botha <er...@gmail.com> wrote:

Hello Guys,

I see a great diagram here http://erpnext.com/files/erpnext_links.html
and it is pretty and the relationships are layed out in the code:

eg:
// define relationships - focus on "source" and "target"
// "source_type" and "target_type" is extra data used to selectively
truncate data set
var _data = [
{"source": "Account", "target_type": "Master", "target": "Cost
Center", "source_type": "Master"},
{"source": "Account", "target_type": null, "target": "GL Entry",
"source_type": "Master"},
{"source": "Account", "target_type": null, "target": "Journal
Voucher", "source_type": "Master"},
{"source": "Account", "target_type": "Master", "target": "Mode of
Payment", "source_type": "Master"},

Is there a data dictionary in the database somewhere that describes
the relationships between database tables and describing relation type
eg: 1 to many; many to many etc...

In addition are there any diagrams showing the tables layed out using
a err diagramming tool like "mysql-workbench"

I would like to contribute to the project some of the systems we have
developed for our internal accounting over the years and teh ER
Diagram is
absolutely necessary to be an effective contributor.

Thanks.

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.
To post to this group, send email to er...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.






You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.


To post to this group, send email to erpnext-dev…@googlegroups.com.



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To post to this group, send email to er…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/aef94f42-2e41-4732-990f-7f1d5a955eb1%40googlegroups.com?hl=en.

For more options, visit https://groups.google.com/groups/opt_out.





--



Twitter: @rushabh_mehta



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.