I was wondering, why Frappé / ERPNext is not using unique IDs as Primary Keys, but Strings (VARCHARS), which are limited to 140 characters by default (which can be changed to 255 with the customize form feature)
I was always convinced, that data should be stored with a PRIMARY KEY with an autoincrement id. Because of the naming series, it is not necessary to use the sql auto increment feature, but I think it should be an integer.
For example: Often there are customers with the same name. Why would someone want to suffix a counting number to the name of a person? Or products with the same name, or multiple shipping or payment addresses for the the customer, and so on.
Maybe there is a reason for not doing this at all. Maybe it is about how Frappé / ERPNext get things done.
I was also wondering of what would happen, when I decide to change the whole system to use those unique IDs.
I am asking for learning more about Frappé / ERPNext and your decisions.
Submittable documents, are versioned using -1, -2 etc according the number of revisions. It is very usefull.
Another case is, you can have distinct ID’s by Company, by Customer, By Territory, things that if you have a serial numeric ID, do you will need replicate it.
I believe that the team, ellected Strings as PK, because the user can customize it as they need, and the naming series is very powerfull, when we mean that the user can customize it.
I don’t really get that point. I know lots of web projects, which are using mysq databases with a unique integer primary key, whichs auto increment is set.
Having an unique integer primary key, for example ID, must not mean the user won’t be able to customize the forms or use the naming series feature.
I am asking further and look forward to understand this and learn about best practices.
I do like the style of frappe using string as a primary key. When you create a new doctype you can use the existing way of assigning primary which is a random unique number or use your own logic assigning primary key. It’s your option. If you want to do that go ahead just use the autoname method and assign your logic for incremental unique key.
I also did not faced major issues, only that I could not import customers with the same name at the first try. I am using the following code to achieve creating customers with the same name.
name = customer_name
suffix = 1
while frappe.db.exists("Customer", name):
name = customer_name + " - " + str(suffix)
suffix = suffix + 1
Maybe it’s useful for someone who is facing the same problem.
@ci2016 about the case of ERPNext I also like the way they use there naming the primary key as @rmehta as said its more easy to read and you do not always make query just getting the name of the foreign key. If you using the autoincrement you have to to make a query just getting name of the foreign field. If you want to change ERPNext the way you want its hard to keep up the changes of the core team.
@rmehta for me its ok to have a bigger size of index. Hard disk Space now is cheap
I have a similar issue with Territory doctype. I’ve a situation where I have duplicate town names, but the system won’t allow that.
I want to do a similar thing to this solution where if the new territory name being persisted exists and it is a group, then add say “A” to the name and if it is not a group add say “B”.
Looking at the frappe api and erpnext code, I am a little stumped as to where to find the code that is responsible for persisting territories. Any heads will be appreciated.
I would create a hook for the Territory doctype, either validate or before_save, maybe on_submit or before_submit (just try whats working best). Pass the {dotted.path.to.function} to a function which checks the availability of the Territory name. If its not available append your suffix and try returning the changed name of the territory. I assume Frappe / ERPNext will use the returned string.
Use my code above:
while frappe.db.exists("Customer", name):
This is the only option to make it really work, when you have 3 times the same territory name.
