Extending id fields to more than 20 chars

[James McGill-5]

Product identifiers in our industry are commonly longer than 20 characters.
id and id-ne fields are defined as 20 character fields for all the databases
I have looked at, and there seems to be some tight binding to the 20
character length in forms and screen templates.   We are wondering how much
work we would need to do, in order to change Product id's to id-longs.  That
sounds pretty big at first glance, since there is so much going on with
Product in so many applications.  Another option would be to standardize
some kind of SKU and cross reference that to a domain-specific product ID
but that would be much less convenient for the users.

Please advise.

--
James McGill
Phoenix AZ

[Ruth Hoffman-2]

Hi James:
I don't think the product id field was ever intended to be used
interchangeably with such identifiers as SKUs. My understanding of the
data model is that the product id is to uniquely identify the product
within the OFBiz domain only. That is how I have always used it. When I
needed to associate SKUs or other identifiers (for example I use ISBNs),
I use the ID's tab within the Catalog Manager.

Maybe someone who did the original data modeling could comment.

Regards,
Ruth

James McGill wrote:

> Product identifiers in our industry are commonly longer than 20 characters.
> id and id-ne fields are defined as 20 character fields for all the databases
> I have looked at, and there seems to be some tight binding to the 20
> character length in forms and screen templates.   We are wondering how much
> work we would need to do, in order to change Product id's to id-longs.  That
> sounds pretty big at first glance, since there is so much going on with
> Product in so many applications.  Another option would be to standardize
> some kind of SKU and cross reference that to a domain-specific product ID
> but that would be much less convenient for the users.
>
> Please advise.
>
>  

[Tim Ruppert]

Some people like to use the product ID for SKUs - so you are not along  
in this request.  here's you can do it easily:

in framework/entity/fieldtype/fieldtype*.xml files

-    <field-type-def type="id" sql-type="VARCHAR(20)" java-
type="String"></field-type-def>
+    <field-type-def type="id" sql-type="VARCHAR(40)" java-
type="String"></field-type-def>


That works like a charm if you choose to go this route.

Cheers,
Ruppert
--
Tim Ruppert
HotWax Media
http://www.hotwaxmedia.com

o:801.649.6594
f:801.649.6595

On Oct 16, 2009, at 2:40 PM, Ruth Hoffman wrote:

> Hi James:
> I don't think the product id field was ever intended to be used  
> interchangeably with such identifiers as SKUs. My understanding of  
> the data model is that the product id is to uniquely identify the  
> product within the OFBiz domain only. That is how I have always used  
> it. When I needed to associate SKUs or other identifiers (for  
> example I use ISBNs), I use the ID's tab within the Catalog Manager.
>
> Maybe someone who did the original data modeling could comment.
>
> Regards,
> Ruth
>
> James McGill wrote:
>> Product identifiers in our industry are commonly longer than 20  
>> characters.
>> id and id-ne fields are defined as 20 character fields for all the  
>> databases
>> I have looked at, and there seems to be some tight binding to the 20
>> character length in forms and screen templates.   We are wondering  
>> how much
>> work we would need to do, in order to change Product id's to id-
>> longs.  That
>> sounds pretty big at first glance, since there is so much going on  
>> with
>> Product in so many applications.  Another option would be to  
>> standardize
>> some kind of SKU and cross reference that to a domain-specific  
>> product ID
>> but that would be much less convenient for the users.
>>
>> Please advise.
>>
>>

[BJ Freeman]

data model book uses Good Ident to define product Ids
see page 75 of volume I Product Identification codes.
also note that ofbiz uses product.internaName VARCHAR(255) for display
and searches.
this leave the productID free to just be internally for Database
relationships. Note ProductID is part of the primary key so there are
limitations to the index for certain databases. One of the reasons not
to change it.

You will find Data Model Handbook vol I invaluable to the Data modeling
of ofbiz, even though some liberties have been taken. I also recommend
vol II



James McGill sent the following on 10/16/2009 1:18 PM:

> Product identifiers in our industry are commonly longer than 20 characters.
> id and id-ne fields are defined as 20 character fields for all the databases
> I have looked at, and there seems to be some tight binding to the 20
> character length in forms and screen templates.   We are wondering how much
> work we would need to do, in order to change Product id's to id-longs.  That
> sounds pretty big at first glance, since there is so much going on with
> Product in so many applications.  Another option would be to standardize
> some kind of SKU and cross reference that to a domain-specific product ID
> but that would be much less convenient for the users.
>
> Please advise.
>

--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.

[James McGill-5]

On Fri, Oct 16, 2009 at 2:37 PM, Tim Ruppert <[hidden email]>wrote:

> Some people like to use the product ID for SKUs - so you are not along in
> this request.  here's you can do it easily:
>
> in framework/entity/fieldtype/fieldtype*.xml files
>
> -    <field-type-def type="id" sql-type="VARCHAR(20)"
> java-type="String"></field-type-def>
> +    <field-type-def type="id" sql-type="VARCHAR(40)"
> java-type="String"></field-type-def>
>
>
> That works like a charm if you choose to go this route.
>

However, 20 chars is hardcoded into forms.  That's what scared me away from
changing fieldtype.

--
James McGill
Phoenix AZ

[Tim Ruppert]

I have not run into any issues with the 20 chars being hardcoded into  
anything on the front end - so sorry if I led you astray.  It's worked  
great for this client for many numbers of years.

Cheers,
Ruppert
--
Tim Ruppert
HotWax Media
http://www.hotwaxmedia.com

o:801.649.6594
f:801.649.6595

On Oct 16, 2009, at 4:04 PM, James McGill wrote:

> On Fri, Oct 16, 2009 at 2:37 PM, Tim Ruppert <[hidden email]
> >wrote:
>
>> Some people like to use the product ID for SKUs - so you are not  
>> along in
>> this request.  here's you can do it easily:
>>
>> in framework/entity/fieldtype/fieldtype*.xml files
>>
>> -    <field-type-def type="id" sql-type="VARCHAR(20)"
>> java-type="String"></field-type-def>
>> +    <field-type-def type="id" sql-type="VARCHAR(40)"
>> java-type="String"></field-type-def>
>>
>>
>> That works like a charm if you choose to go this route.
>>
>
> However, 20 chars is hardcoded into forms.  That's what scared me  
> away from
> changing fieldtype.
>
> --
> James McGill
> Phoenix AZ

[Tim Ruppert]

Please note that what Ruth and BJ are mentioning here James is likely  
the preferred way to do this.  The Good Identifiers is the way I'd  
push the client nowadays, but they do like this info in their IDs, so  
no need to push.

Cheers,
Ruppert
--
Tim Ruppert
HotWax Media
http://www.hotwaxmedia.com

o:801.649.6594
f:801.649.6595

On Oct 16, 2009, at 3:56 PM, BJ Freeman wrote:

> data model book uses Good Ident to define product Ids
> see page 75 of volume I Product Identification codes.
> also note that ofbiz uses product.internaName VARCHAR(255) for display
> and searches.
> this leave the productID free to just be internally for Database
> relationships. Note ProductID is part of the primary key so there are
> limitations to the index for certain databases. One of the reasons not
> to change it.
>
> You will find Data Model Handbook vol I invaluable to the Data  
> modeling
> of ofbiz, even though some liberties have been taken. I also recommend
> vol II
>
>
>
> James McGill sent the following on 10/16/2009 1:18 PM:
>> Product identifiers in our industry are commonly longer than 20  
>> characters.
>> id and id-ne fields are defined as 20 character fields for all the  
>> databases
>> I have looked at, and there seems to be some tight binding to the 20
>> character length in forms and screen templates.   We are wondering  
>> how much
>> work we would need to do, in order to change Product id's to id-
>> longs.  That
>> sounds pretty big at first glance, since there is so much going on  
>> with
>> Product in so many applications.  Another option would be to  
>> standardize
>> some kind of SKU and cross reference that to a domain-specific  
>> product ID
>> but that would be much less convenient for the users.
>>
>> Please advise.
>>
>
> --
> BJ Freeman
> http://www.businessesnetwork.com/automation
> http://bjfreeman.elance.com
> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
> Systems Integrator.
>