Foreign keys
40 views
Skip to first unread message
Damian Dimmich
Oct 9, 2013, 4:56:05 AM10/9/13
to erpnext-dev...@googlegroups.com
Hi,
It seems that foreign keys are not defined anywhere in the database... It would be good to have these defined, otherwise you have no more data integrity than an excel spreadsheet. Is there a particular reason for this, or is that just what comes out of the database dump that erpnext makes when creating a backup?
Thanks,
Damian
It seems that foreign keys are not defined anywhere in the database... It would be good to have these defined, otherwise you have no more data integrity than an excel spreadsheet. Is there a particular reason for this, or is that just what comes out of the database dump that erpnext makes when creating a backup?
Thanks,
Damian
rushabh
Oct 9, 2013, 9:08:51 AM10/9/13
to erpnext-dev...@googlegroups.com
Foreign keys are managed by the framework - "Link" type fields are all foreign keys.
The reason for this is MySQL automatically indexes foreign keys and in some transactions we have more than 10 foreign keys and this kills performance.
Damian Dimmich
Oct 9, 2013, 10:18:41 AM10/9/13
to erpnext-dev...@googlegroups.com
Hi,
Ok - makes sense that it's slow - type link is a varchar 180 - indexing that is going to have a huge penalty and will create huge indicies.
From webnotes.model.db_schema on line 30 link is defined as (assuming I am looking in the right place):
,'link': ('varchar', '180')
If you want to have efficient foreign keys these need to be integers - quite a few orm's (ie rails orm, and django orm) automatically define 'id' columns for all
tables and use these are primary keys - having had another look at the database it looks like you use 'name' (varchar 120) as the primary key. This will also
create indexes for 'name' and is also going to be slow compared to having an integer based primary key.
Having not looked at enough of the code I can't tell how hard it would be to adjust it to use integers for record lookups, but I suspect this is something that you
will have to do for larger ERP installations or they will become unusable.
By changing your foreign keys to integers you'll be able to have them indexed, foreign key constraints enabled and still have manageable index sizes.
In the longer term - it may also be worth looking into an ORM like SQLAlchemy to replace the one you have built for this app - it will let you run erpnext against
other databases as well which might be something your future/bigger enterprise customers might want.
Best,
Damian
Ok - makes sense that it's slow - type link is a varchar 180 - indexing that is going to have a huge penalty and will create huge indicies.
From webnotes.model.db_schema on line 30 link is defined as (assuming I am looking in the right place):
,'link': ('varchar', '180')
If you want to have efficient foreign keys these need to be integers - quite a few orm's (ie rails orm, and django orm) automatically define 'id' columns for all
tables and use these are primary keys - having had another look at the database it looks like you use 'name' (varchar 120) as the primary key. This will also
create indexes for 'name' and is also going to be slow compared to having an integer based primary key.
Having not looked at enough of the code I can't tell how hard it would be to adjust it to use integers for record lookups, but I suspect this is something that you
will have to do for larger ERP installations or they will become unusable.
By changing your foreign keys to integers you'll be able to have them indexed, foreign key constraints enabled and still have manageable index sizes.
In the longer term - it may also be worth looking into an ORM like SQLAlchemy to replace the one you have built for this app - it will let you run erpnext against
other databases as well which might be something your future/bigger enterprise customers might want.
Best,
Damian
Rushabh Mehta
Oct 10, 2013, 12:57:31 AM10/10/13
to erpnext-dev...@googlegroups.com
Damian,
Yeah we have never looked into this recently - thanks for clarifying we will probably fix this at a later point. It will involve a major rewrite though!
Again moving to SQLAlchemy is another big project - no real motivation for that right now. But yes it will help getting things even more standardized.
best,
Rushabh
--
Note:
If you are posting an issue,
1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.
End of Note
---
You received this message because you are subscribed to a topic in the Google Groups "ERPNext Developer Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/erpnext-developer-forum/DiQilqJ0LBk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to erpnext-developer...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
Rushabh Mehta
Oct 10, 2013, 12:57:31 AM10/10/13
to erpnext-dev...@googlegroups.com
Damian,
Yeah we have never looked into this recently - thanks for clarifying we will probably fix this at a later point. It will involve a major rewrite though!
Again moving to SQLAlchemy is another big project - no real motivation for that right now. But yes it will help getting things even more standardized.
best,
Rushabh
On 09-Oct-2013, at 7:48 PM, Damian Dimmich <dam...@tauri-tec.com> wrote:
Reply all
Reply to author
Forward
0 new messages