Users - Changing Primary Key Values
Locked
Users - Changing Primary Key Values
 
|
A client brought in a new IT Manager that wants to
standardize productIds. He wants to use the initial character of the
productId to distinguish between different product types so he can run reports
in Excel without having to export a bunch of fields. He also wants to go
back and change between 50-100 existing productIds. I explained that changing a primary key is not
recommended. It’s just a unique identifier that points to the real
data, and it shouldn’t matter what the value of the primary key is.
Even if we went through all of the thousands of inventory, OrderItem, price,
and other records to ensure that the productId was changed in every instance,
customer’s receipts and old reports would still show the old productId.
The point of having a primary key is that names, descriptions, and relations
can change but the key is the unchanging foundation that holds it all together. I’m hoping some of the other OFBiz consultants and
developers could help me with 2 questions.
Thank you! sterling _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
Re: Users - Changing Primary Key Values
|
|
There are many Identifier for a Product, the first suggestion is to use
a new view to use those Identifiers for the Export. To change the ones already in ofbiz would require exporting all the entities in to xml, using an editor to change them then clean the Database and re-import using install. Not suggested. Next would be to clean the database, change the data in the xml's before importing. as far as the Primary Keys you would have to change the service that makes the ID number to fit your customers requirements. The Key is a string so can be anything you want. So you could add the this to new product. Sterling Okura sent the following on 3/26/06 12:50 PM: > A client brought in a new IT Manager that wants to standardize productIds. > He wants to use the initial character of the productId to distinguish > between different product types so he can run reports in Excel without > having to export a bunch of fields. He also wants to go back and change > between 50-100 existing productIds. > > > > I explained that changing a primary key is not recommended. It's just a > unique identifier that points to the real data, and it shouldn't matter what > the value of the primary key is. Even if we went through all of the > thousands of inventory, OrderItem, price, and other records to ensure that > the productId was changed in every instance, customer's receipts and old > reports would still show the old productId. The point of having a primary > key is that names, descriptions, and relations can change but the key is the > unchanging foundation that holds it all together. > > > > I'm hoping some of the other OFBiz consultants and developers could help me > with 2 questions. > > > > 1. Is changing primary keys that big of a deal? Am I just being > stubborn? The manager said he went through a huge swap of over a million > primary keys at his last job. It looks like he just wants to put things > into a familiar framework like he had at his previous job. > > > > 2. What are some alternative options? I suggested using another field > like a model # field, and creating views that allow sales and inventory > queries by that new field. Any other ideas? > > > > Thank you! > > sterling > > > > > > > > > > > ------------------------------------------------------------------------ > > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
Re: Users - Changing Primary Key Values
|
|
BJ, thank you for the prompt reply. Since ProductId's are user-entered (not
generated) I don' have to worry about modifying any services. I agree with you on using one of the other identifier fields instead of changing the actual productId. The exporting, modifying and reimporting of data will be a headache. I should be able to convince the client to use another identifier. Thank you sir, sterling -----Original Message----- From: BJ Freeman [mailto:[hidden email]] Sent: Sunday, March 26, 2006 2:04 PM To: OFBiz Users / Usage Discussion Subject: Re: [OFBiz] Users - Changing Primary Key Values There are many Identifier for a Product, the first suggestion is to use a new view to use those Identifiers for the Export. To change the ones already in ofbiz would require exporting all the entities in to xml, using an editor to change them then clean the Database and re-import using install. Not suggested. Next would be to clean the database, change the data in the xml's before importing. as far as the Primary Keys you would have to change the service that makes the ID number to fit your customers requirements. The Key is a string so can be anything you want. So you could add the this to new product. Sterling Okura sent the following on 3/26/06 12:50 PM: > A client brought in a new IT Manager that wants to standardize productIds. > He wants to use the initial character of the productId to distinguish > between different product types so he can run reports in Excel without > having to export a bunch of fields. He also wants to go back and change > between 50-100 existing productIds. > > > > I explained that changing a primary key is not recommended. It's just a > unique identifier that points to the real data, and it shouldn't matter > the value of the primary key is. Even if we went through all of the > thousands of inventory, OrderItem, price, and other records to ensure that > the productId was changed in every instance, customer's receipts and old > reports would still show the old productId. The point of having a primary > key is that names, descriptions, and relations can change but the key is the > unchanging foundation that holds it all together. > > > > I'm hoping some of the other OFBiz consultants and developers could help me > with 2 questions. > > > > 1. Is changing primary keys that big of a deal? Am I just being > stubborn? The manager said he went through a huge swap of over a million > primary keys at his last job. It looks like he just wants to put things > into a familiar framework like he had at his previous job. > > > > 2. What are some alternative options? I suggested using another field > like a model # field, and creating views that allow sales and inventory > queries by that new field. Any other ideas? > > > > Thank you! > > sterling > > > > > > > > > > > ------------------------------------------------------------------------ > > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
Re: Users - Changing Primary Key Values
|
|
In reply to this post by Sterling Okura
Usually when they see a price tag in the thousand or dollars and months
of verifying data, they usually opt for the easier method. Good luck :) Sterling Okura sent the following on 3/26/06 1:33 PM: > BJ, thank you for the prompt reply. Since ProductId's are user-entered (not > generated) I don' have to worry about modifying any services. > > I agree with you on using one of the other identifier fields instead of > changing the actual productId. The exporting, modifying and reimporting of > data will be a headache. I should be able to convince the client to use > another identifier. > > Thank you sir, > sterling > > -----Original Message----- > From: BJ Freeman [mailto:[hidden email]] > Sent: Sunday, March 26, 2006 2:04 PM > To: OFBiz Users / Usage Discussion > Subject: Re: [OFBiz] Users - Changing Primary Key Values > > There are many Identifier for a Product, the first suggestion is to use > a new view to use those Identifiers for the Export. > > To change the ones already in ofbiz would require exporting all the > entities in to xml, using an editor to change them then clean the > Database and re-import using install. Not suggested. > > Next would be to clean the database, change the data in the xml's before > importing. > > as far as the Primary Keys you would have to change the service that > makes the ID number to fit your customers requirements. The Key is a > string so can be anything you want. So you could add the this to new > product. > > Sterling Okura sent the following on 3/26/06 12:50 PM: > >>A client brought in a new IT Manager that wants to standardize productIds. >>He wants to use the initial character of the productId to distinguish >>between different product types so he can run reports in Excel without >>having to export a bunch of fields. He also wants to go back and change >>between 50-100 existing productIds. >> >> >> >>I explained that changing a primary key is not recommended. It's just a >>unique identifier that points to the real data, and it shouldn't matter > > what > >>the value of the primary key is. Even if we went through all of the >>thousands of inventory, OrderItem, price, and other records to ensure that >>the productId was changed in every instance, customer's receipts and old >>reports would still show the old productId. The point of having a primary >>key is that names, descriptions, and relations can change but the key is > > the > >>unchanging foundation that holds it all together. >> >> >> >>I'm hoping some of the other OFBiz consultants and developers could help > > me > >>with 2 questions. >> >> >> >>1. Is changing primary keys that big of a deal? Am I just being >>stubborn? The manager said he went through a huge swap of over a million >>primary keys at his last job. It looks like he just wants to put things >>into a familiar framework like he had at his previous job. >> >> >> >>2. What are some alternative options? I suggested using another field >>like a model # field, and creating views that allow sales and inventory >>queries by that new field. Any other ideas? >> >> >> >>Thank you! >> >>sterling >> >> >> >> >> >> >> >> >> >> >>------------------------------------------------------------------------ >> >> >>_______________________________________________ >>Users mailing list >>[hidden email] >>http://lists.ofbiz.org/mailman/listinfo/users > > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users > > > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users > _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
Re: Users - Changing Primary Key Values
|
|
In reply to this post by Sterling Okura
Sterling, It sounds like you're dealing with a nightmare manager guy... I've seen this type of stuff happen in medium sized organizations that are big enough to afford silly things, and small enough to not have people with sufficient experience or oversight to keep them from happening, or people that have only been involved with "less modern" systems... Most ideas like this come from the highly de-normalized days when systems couldn't handle the overhead of more flexible data models. This results in very customized systems that are very expensive to change and nearly impossible to reuse. Using a separate field, or even better something like a category relationship or feature application, is a much better way to track this sort of thing. Putting this sort of information into an export or report is not nearly as difficult as a primary key change. If you're really lucky the guy will have said something like "we did this before for a million products, what is the big deal with doing it for 100"? Unless it is a fresh database with no history, be it 100 or 1,000,000 it requires about the same amount of human effort because it is resolving the relationships and such that requires work. It's not something you can reasonably do by hand. BTW, the export/change/import is one way to do it, but a better way is typically to do it in the database with SQL if there are a large number of records (things will take seconds or minutes instead of hours to run). -David Sterling Okura wrote: > A client brought in a new IT Manager that wants to standardize > productIds. He wants to use the initial character of the productId to > distinguish between different product types so he can run reports in > Excel without having to export a bunch of fields. He also wants to go > back and change between 50-100 existing productIds. > > > > I explained that changing a primary key is not recommended. It’s just a > unique identifier that points to the real data, and it shouldn’t matter > what the value of the primary key is. Even if we went through all of > the thousands of inventory, OrderItem, price, and other records to > ensure that the productId was changed in every instance, customer’s > receipts and old reports would still show the old productId. The point > of having a primary key is that names, descriptions, and relations can > change but the key is the unchanging foundation that holds it all together. > > > > I’m hoping some of the other OFBiz consultants and developers could help > me with 2 questions. > > > > 1. Is changing primary keys that big of a deal? Am I just being > stubborn? The manager said he went through a huge swap of over a > million primary keys at his last job. It looks like he just wants > to put things into a familiar framework like he had at his > previous job. > > > > 2. What are some alternative options? I suggested using another > field like a model # field, and creating views that allow sales > and inventory queries by that new field. Any other ideas? > > > > Thank you! > > sterling > > > > > > > > > ------------------------------------------------------------------------ > > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
Re: Users - Changing Primary Key Values
|
|
David,
Thank you for the response sir. You called the scenario exactly. The manager has been working with systems for over 20 years, is great with organization and operations, works well with Excel and flat-files, but doesn't seem to grasp modern data modeling concepts. To the manager's credit he seems open-minded to other ideas and will listen to feedback. He's just going with what he's familiar and comfortable with. Thank you for the advice on using SQL rather than import/exports to change the productIds. Search & replace with massive text files would be painful. I wasn't sure if PostgreSQL would support altering primary keys, but I suppose that if I were to catch all affected tables at the same time then the constraints would remain intact. I'm still crossing my fingers that I can talk him out of it. It just seems like a step backwards or at least a waste of resources. Thank you sir! sterling -----Original Message----- From: David E. Jones [mailto:[hidden email]] Sent: Sunday, March 26, 2006 4:44 PM To: OFBiz Users / Usage Discussion Subject: Re: [OFBiz] Users - Changing Primary Key Values Sterling, It sounds like you're dealing with a nightmare manager guy... I've seen this type of stuff happen in medium sized organizations that are big enough to afford silly things, and small enough to not have people with sufficient experience or oversight to keep them from happening, or people that have only been involved with "less modern" systems... Most ideas like this come from the highly de-normalized days when systems couldn't handle the overhead of more flexible data models. This results in very customized systems that are very expensive to change and nearly impossible to reuse. Using a separate field, or even better something like a category relationship or feature application, is a much better way to track this sort of thing. Putting this sort of information into an export or report is not nearly as difficult as a primary key change. If you're really lucky the guy will have said something like "we did this before for a million products, what is the big deal with doing it for 100"? Unless it is a fresh database with no history, be it 100 or 1,000,000 it requires about the same amount of human effort because it is resolving the relationships and such that requires work. It's not something you can reasonably do by hand. BTW, the export/change/import is one way to do it, but a better way is typically to do it in the database with SQL if there are a large number of records (things will take seconds or minutes instead of hours to run). -David Sterling Okura wrote: > A client brought in a new IT Manager that wants to standardize > productIds. He wants to use the initial character of the productId to > distinguish between different product types so he can run reports in > Excel without having to export a bunch of fields. He also wants to go > back and change between 50-100 existing productIds. > > > > I explained that changing a primary key is not recommended. It's just a > unique identifier that points to the real data, and it shouldn't matter > what the value of the primary key is. Even if we went through all of > the thousands of inventory, OrderItem, price, and other records to > ensure that the productId was changed in every instance, customer's > receipts and old reports would still show the old productId. The point > of having a primary key is that names, descriptions, and relations can > change but the key is the unchanging foundation that holds it all > > > > I'm hoping some of the other OFBiz consultants and developers could help > me with 2 questions. > > > > 1. Is changing primary keys that big of a deal? Am I just being > stubborn? The manager said he went through a huge swap of over a > million primary keys at his last job. It looks like he just wants > to put things into a familiar framework like he had at his > previous job. > > > > 2. What are some alternative options? I suggested using another > field like a model # field, and creating views that allow sales > and inventory queries by that new field. Any other ideas? > > > > Thank you! > > sterling > > > > > > > > > ------------------------------------------------------------------------ > > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
Re: Users - Changing Primary Key Values
|
|
In reply to this post by Sterling Okura
Going the SQL route you won't be able to just change primary keys typically. Sometimes you can remove the primary key constraint and then change it, but sometimes (depending on the database and other misc things) it is necessary to copy the record to the new pk. The best approach is usually to disable or remove the primary key constraint and the foreign key constraints, and then change it and then when you try to turn it back it will tell about about bad data. This isn't 100% fool proof though as not all data referring to a primary key field has a foreign key or anything making it easy to track them down. -David Sterling Okura wrote: > David, > > Thank you for the response sir. You called the scenario exactly. The > manager has been working with systems for over 20 years, is great with > organization and operations, works well with Excel and flat-files, but > doesn't seem to grasp modern data modeling concepts. To the manager's > credit he seems open-minded to other ideas and will listen to feedback. > He's just going with what he's familiar and comfortable with. > > Thank you for the advice on using SQL rather than import/exports to change > the productIds. Search & replace with massive text files would be painful. > I wasn't sure if PostgreSQL would support altering primary keys, but I > suppose that if I were to catch all affected tables at the same time then > the constraints would remain intact. I'm still crossing my fingers that I > can talk him out of it. It just seems like a step backwards or at least a > waste of resources. > > Thank you sir! > sterling > > -----Original Message----- > From: David E. Jones [mailto:[hidden email]] > Sent: Sunday, March 26, 2006 4:44 PM > To: OFBiz Users / Usage Discussion > Subject: Re: [OFBiz] Users - Changing Primary Key Values > > > Sterling, > > It sounds like you're dealing with a nightmare manager guy... I've seen this > type of stuff happen in medium sized organizations that are big enough to > afford silly things, and small enough to not have people with sufficient > experience or oversight to keep them from happening, or people that have > only been involved with "less modern" systems... > > Most ideas like this come from the highly de-normalized days when systems > couldn't handle the overhead of more flexible data models. This results in > very customized systems that are very expensive to change and nearly > impossible to reuse. > > Using a separate field, or even better something like a category > relationship or feature application, is a much better way to track this sort > of thing. Putting this sort of information into an export or report is not > nearly as difficult as a primary key change. > > If you're really lucky the guy will have said something like "we did this > before for a million products, what is the big deal with doing it for 100"? > Unless it is a fresh database with no history, be it 100 or 1,000,000 it > requires about the same amount of human effort because it is resolving the > relationships and such that requires work. It's not something you can > reasonably do by hand. BTW, the export/change/import is one way to do it, > but a better way is typically to do it in the database with SQL if there are > a large number of records (things will take seconds or minutes instead of > hours to run). > > -David > > > Sterling Okura wrote: >> A client brought in a new IT Manager that wants to standardize >> productIds. He wants to use the initial character of the productId to >> distinguish between different product types so he can run reports in >> Excel without having to export a bunch of fields. He also wants to go >> back and change between 50-100 existing productIds. >> >> >> >> I explained that changing a primary key is not recommended. It's just a >> unique identifier that points to the real data, and it shouldn't matter >> what the value of the primary key is. Even if we went through all of >> the thousands of inventory, OrderItem, price, and other records to >> ensure that the productId was changed in every instance, customer's >> receipts and old reports would still show the old productId. The point >> of having a primary key is that names, descriptions, and relations can >> change but the key is the unchanging foundation that holds it all > together. >> >> >> I'm hoping some of the other OFBiz consultants and developers could help >> me with 2 questions. >> >> >> >> 1. Is changing primary keys that big of a deal? Am I just being >> stubborn? The manager said he went through a huge swap of over a >> million primary keys at his last job. It looks like he just wants >> to put things into a familiar framework like he had at his >> previous job. >> >> >> >> 2. What are some alternative options? I suggested using another >> field like a model # field, and creating views that allow sales >> and inventory queries by that new field. Any other ideas? >> >> >> >> Thank you! >> >> sterling >> >> >> >> >> >> >> >> >> ------------------------------------------------------------------------ >> >> >> _______________________________________________ >> Users mailing list >> [hidden email] >> http://lists.ofbiz.org/mailman/listinfo/users > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users > > > > _______________________________________________ > Users mailing list > [hidden email] > http://lists.ofbiz.org/mailman/listinfo/users _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |