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
–
Note:
If you are posting an issue,
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.
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 the Google Groups "ERPNext Developer Forum" group.
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.
On Wednesday, October 9, 2013 2:26:05 PM UTC+5:30, Damian Dimmich wrote:
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
–
Note:
If you are posting an issue,
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.
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 the Google Groups "ERPNext Developer Forum" group.
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
On Wednesday, October 9, 2013 5:08:51 PM UTC+4, rushabh wrote:
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.
On Wednesday, October 9, 2013 2:26:05 PM UTC+5:30, Damian Dimmich wrote:
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
–
Note:
If you are posting an issue,
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.
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 the Google Groups "ERPNext Developer Forum" group.
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.
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
On Wednesday, October 9, 2013 5:08:51 PM UTC+4, rushabh wrote:
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.
On Wednesday, October 9, 2013 2:26:05 PM UTC+5:30, Damian Dimmich wrote:
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
–
Note:
If you are posting an issue,
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.
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.
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.
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 the Google Groups "ERPNext Developer Forum" group.
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.
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
On Wednesday, October 9, 2013 5:08:51 PM UTC+4, rushabh wrote:
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.
On Wednesday, October 9, 2013 2:26:05 PM UTC+5:30, Damian Dimmich wrote:
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
–
Note:
If you are posting an issue,
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.
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.
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.
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 the Google Groups "ERPNext Developer Forum" group.
Was this ever looked at in the previous 8 years? I have some SQL knowledge but am by no means an expert on this. But not having proper foreign keys seems to be a rather unclean implementation. Pushing this out to the future won’t make things easier I assume. Quite some technical debt…
@rmehta Any update on the DB design & foreign keys ?
It’s not any good to have DB relation based on varchar and strings
Also It’s very unclean to not have Many2one <> One2many relations between tables and child tables , ex: invoice_item should have a link to its invoice and sale_item should have a link to its sale
You built great business apps on unclean technical DB design, when we can expect correct DB design within the framework ?