Foreign keys

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,

  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 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.

    For more options, visit https://groups.google.com/groups/opt_out.
1 Like
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,

  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 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.

    For more options, visit https://groups.google.com/groups/opt_out.
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

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,

  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 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.

    For more options, visit https://groups.google.com/groups/opt_out.
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




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

On 09-Oct-2013, at 7:48 PM, Damian Dimmich <da...@tauri-tec.com> wrote:

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

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,

  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-forum+un…@googlegroups.com.

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



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 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.

    For more options, visit https://groups.google.com/groups/opt_out.
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




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

On 09-Oct-2013, at 7:48 PM, Damian Dimmich <da...@tauri-tec.com> wrote:

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

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,

  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-forum+un…@googlegroups.com.

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



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 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.

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

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 ?

Regards.

2 Likes

IMHO, DataBase re-design must be taken in top priority, and might be announed as major revision for the Frappe.

Was there any progress on this front @rmehta. @hsra thanks for bringing up this point as I am evaluating ERPNext.

2 Likes