Use UUID for ProductID

[Ofbiz 开发]

Hi everyone,

I am considering using Java UUID for the ProductID (
http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html ), but that
requires at least varchar (32), and the current ProductID field in database
is only varchar(20), how hard is it to change to varchar(32) please? Is
there any plan in Ofbiz trunk to change this to varchar(32) please? It
seems better than using sequence.

Thanks.

Kind Regards

Fong

[Adrian Crum-3]

What's wrong with the existing sequence generator? Why do you think UUID
is better?

-Adrian

On 4/30/2013 11:38 AM, Ofbiz 开发 wrote:

> Hi everyone,
>
> I am considering using Java UUID for the ProductID (
> http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html ), but that
> requires at least varchar (32), and the current ProductID field in database
> is only varchar(20), how hard is it to change to varchar(32) please? Is
> there any plan in Ofbiz trunk to change this to varchar(32) please? It
> seems better than using sequence.
>
> Thanks.
>
> Kind Regards
>
> Fong
>

[Mike Z]

Compare the following three files:

framework/entity/fieldtype/fieldtypemysql.xml
framework/entity/fieldtype/fieldtypepostgres.xml
framework/entity/fieldtype/fieldtypepostnew.xml

I bet you are using mysql, or maybe even postgres, which has very LIMITED
key value lengths.  In the real world, you never know what key length you
may need in the future, so why limit yourself?  Now take a look at
"postnew", which uses a generic "text" type for almost all the fields.  It
allows ANY key length, which allows the flexibility to create logical
relations, even for part numbers that may be 60 characters or so.

I learned this the hard way, when I came across long part numbers, and
struggled to figure out a way to logically store them.  Back then I was
using mysql.  I made the hard conversion to postgres, using
"postnew", defined in:

framework/entity/config/entityengine.xml:
<datasource name="localpostnew"
             field-type-name="postnew"

And never had a problem since.  You can then use UUID for anything.



On Tue, Apr 30, 2013 at 3:38 AM, Ofbiz 开发 <[hidden email]> wrote:

> Hi everyone,
>
> I am considering using Java UUID for the ProductID (
> http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html ), but that
> requires at least varchar (32), and the current ProductID field in database
> is only varchar(20), how hard is it to change to varchar(32) please? Is
> there any plan in Ofbiz trunk to change this to varchar(32) please? It
> seems better than using sequence.
>
> Thanks.
>
> Kind Regards
>
> Fong
>

[Ofbiz 开发]

Hi Mike,

Thank you very much for your detailed reply. I hasn't came across TEXT data
type before, I just Google it and seems not a ISO standard data type, only
specific to postgreSQL, but it does have the advantage you mentioned. And
yes we are building a new system and will be using PostgreSQL.

So is this fieldtypepostnew.xml a recommended one to use for all new OFBIZ
system please? Will it have any bad side effect since it's not a standard
data type please? E.g. in future upgrade etc... Thank you very much!

Kind Regards

Fong



On Wed, May 1, 2013 at 3:52 AM, Mike <[hidden email]> wrote:

> Compare the following three files:
>
> framework/entity/fieldtype/fieldtypemysql.xml
> framework/entity/fieldtype/fieldtypepostgres.xml
> framework/entity/fieldtype/fieldtypepostnew.xml
>
> I bet you are using mysql, or maybe even postgres, which has very LIMITED
> key value lengths.  In the real world, you never know what key length you
> may need in the future, so why limit yourself?  Now take a look at
> "postnew", which uses a generic "text" type for almost all the fields.  It
> allows ANY key length, which allows the flexibility to create logical
> relations, even for part numbers that may be 60 characters or so.
>
> I learned this the hard way, when I came across long part numbers, and
> struggled to figure out a way to logically store them.  Back then I was
> using mysql.  I made the hard conversion to postgres, using
> "postnew", defined in:
>
> framework/entity/config/entityengine.xml:
> <datasource name="localpostnew"
>              field-type-name="postnew"
>
> And never had a problem since.  You can then use UUID for anything.
>
>
>
> On Tue, Apr 30, 2013 at 3:38 AM, Ofbiz 开发 <[hidden email]> wrote:
>
> > Hi everyone,
> >
> > I am considering using Java UUID for the ProductID (
> > http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html ), but that
> > requires at least varchar (32), and the current ProductID field in
> database
> > is only varchar(20), how hard is it to change to varchar(32) please? Is
> > there any plan in Ofbiz trunk to change this to varchar(32) please? It
> > seems better than using sequence.
> >
> > Thanks.
> >
> > Kind Regards
> >
> > Fong
> >
>

[Adrian Crum-3]

On 4/30/2013 4:52 PM, Mike wrote:

> Compare the following three files:
>
> framework/entity/fieldtype/fieldtypemysql.xml
> framework/entity/fieldtype/fieldtypepostgres.xml
> framework/entity/fieldtype/fieldtypepostnew.xml
>
> I bet you are using mysql, or maybe even postgres, which has very LIMITED
> key value lengths.  In the real world, you never know what key length you
> may need in the future, so why limit yourself?  Now take a look at
> "postnew", which uses a generic "text" type for almost all the fields.  It
> allows ANY key length, which allows the flexibility to create logical
> relations, even for part numbers that may be 60 characters or so.
>
> I learned this the hard way, when I came across long part numbers, and
> struggled to figure out a way to logically store them.  Back then I was
> using mysql.

Part numbers are a GOOD IDENTIFICATION. So, you wouldn't put the part
number in a 20 character ID field. Instead, you would use the standard
ID sequence generator for the PRODUCT, then link the PRODUCT to a GOOD
IDENTIFICATION.

-Adrian

[Ofbiz 开发]

Hi Adrian,

So at the moment ProductID is type "id-ne", is it possible to change it to
type "id-long-ne" please? Will it affect anything else or other bad side
effect please?

And do you think using the fieldtypepostnew.xml is not a good idea, even
though it's a new system? Thank you very much for sharing your thoughts.

Kind Regards

Fong

On Wed, May 1, 2013 at 10:41 PM, Adrian Crum <
[hidden email]> wrote:

>
> On 4/30/2013 4:52 PM, Mike wrote:
>
>> Compare the following three files:
>>
>> framework/entity/fieldtype/**fieldtypemysql.xml
>> framework/entity/fieldtype/**fieldtypepostgres.xml
>> framework/entity/fieldtype/**fieldtypepostnew.xml
>>
>> I bet you are using mysql, or maybe even postgres, which has very LIMITED
>> key value lengths.  In the real world, you never know what key length you
>> may need in the future, so why limit yourself?  Now take a look at
>> "postnew", which uses a generic "text" type for almost all the fields.  It
>> allows ANY key length, which allows the flexibility to create logical
>> relations, even for part numbers that may be 60 characters or so.
>>
>> I learned this the hard way, when I came across long part numbers, and
>> struggled to figure out a way to logically store them.  Back then I was
>> using mysql.
>>
>
> Part numbers are a GOOD IDENTIFICATION. So, you wouldn't put the part
> number in a 20 character ID field. Instead, you would use the standard ID
> sequence generator for the PRODUCT, then link the PRODUCT to a GOOD
> IDENTIFICATION.
>
> -Adrian
>
>

[Adrian Crum-3]

I would recommend leaving the data model as-is. A 20 character ID
accommodates thousands of trillions of products - certainly sufficient
for most businesses.

-Adrian

On 5/2/2013 4:33 AM, Ofbiz 开发 wrote:

> Hi Adrian,
>
> So at the moment ProductID is type "id-ne", is it possible to change it to
> type "id-long-ne" please? Will it affect anything else or other bad side
> effect please?
>
> And do you think using the fieldtypepostnew.xml is not a good idea, even
> though it's a new system? Thank you very much for sharing your thoughts.
>
> Kind Regards
>
> Fong
>
> On Wed, May 1, 2013 at 10:41 PM, Adrian Crum <
> [hidden email]> wrote:
>
>> On 4/30/2013 4:52 PM, Mike wrote:
>>
>>> Compare the following three files:
>>>
>>> framework/entity/fieldtype/**fieldtypemysql.xml
>>> framework/entity/fieldtype/**fieldtypepostgres.xml
>>> framework/entity/fieldtype/**fieldtypepostnew.xml
>>>
>>> I bet you are using mysql, or maybe even postgres, which has very LIMITED
>>> key value lengths.  In the real world, you never know what key length you
>>> may need in the future, so why limit yourself?  Now take a look at
>>> "postnew", which uses a generic "text" type for almost all the fields.  It
>>> allows ANY key length, which allows the flexibility to create logical
>>> relations, even for part numbers that may be 60 characters or so.
>>>
>>> I learned this the hard way, when I came across long part numbers, and
>>> struggled to figure out a way to logically store them.  Back then I was
>>> using mysql.
>>>
>> Part numbers are a GOOD IDENTIFICATION. So, you wouldn't put the part
>> number in a 20 character ID field. Instead, you would use the standard ID
>> sequence generator for the PRODUCT, then link the PRODUCT to a GOOD
>> IDENTIFICATION.
>>
>> -Adrian
>>
>>

[Mike Z]

This is certainly one way to do it.

In my case, I prefer to use "SUPPLIER_ID-SUPPLIER_PN" for the PRODUCT_ID,
and use the same format for electronictext, contentid, contentassoc, etc.
 It allows me to logically organize the product_id, and also allows me to
blow away a product if I need to.  Example:

     product_id     | product_type_id | primary_product_category_id
 10000-798936838421 | FINISHED_GOOD   | 10003                       |

 10000-798936838421Dru          | ELECTRONIC_TEXT       |
    |                  |                |                  |
                                 | ru            |

The above supplier "10000", and PN (UPC) is the PRODUCT_ID, and the above
ELECTRONIC_TEXT  entry is the russian description (Dru) of the item.

A sample similar to the above was in DemoData, so I went with it.

On Wed, May 1, 2013 at 3:41 AM, Adrian Crum <
[hidden email]> wrote:

>
> On 4/30/2013 4:52 PM, Mike wrote:
>
>> Compare the following three files:
>>
>> framework/entity/fieldtype/**fieldtypemysql.xml
>> framework/entity/fieldtype/**fieldtypepostgres.xml
>> framework/entity/fieldtype/**fieldtypepostnew.xml
>>
>> I bet you are using mysql, or maybe even postgres, which has very LIMITED
>> key value lengths.  In the real world, you never know what key length you
>> may need in the future, so why limit yourself?  Now take a look at
>> "postnew", which uses a generic "text" type for almost all the fields.  It
>> allows ANY key length, which allows the flexibility to create logical
>> relations, even for part numbers that may be 60 characters or so.
>>
>> I learned this the hard way, when I came across long part numbers, and
>> struggled to figure out a way to logically store them.  Back then I was
>> using mysql.
>>
>
> Part numbers are a GOOD IDENTIFICATION. So, you wouldn't put the part
> number in a 20 character ID field. Instead, you would use the standard ID
> sequence generator for the PRODUCT, then link the PRODUCT to a GOOD
> IDENTIFICATION.
>
> -Adrian
>
>

[Paul Foxworthy]

I would also urge caution in changing keys.

There is one advantage in using UUIDs. If you have distributed or replicated data, you can generate new rows in different locations and you can be sure there will be no collisions when you synchronize later on.

There are disadvantages to UUIDs too. Compactness matters in keys. An excessive key size means database index entries are big, and therefore less will fit in pages and there will be more disk I/O to search the index.

UUIDs are random, so in tree indexes pages will be split at random, and there will be considerable fragmentation. In contrast, if the key is continually increasing, all the action is in the bottom right hand corner of a tree index, and (assuming the keys are stable and a low number of deletes) there won't be many random splits elsewhere.

So UUIDs really only make sense if you have distributed or replicated data, and that's not typical for OFBiz.

If you really wanted to use UUIDs, it might be possible to store them in binary form, where they would occupy 16 bytes (128 bits).

Cheers

Paul Foxworthy

Mike Z wrote

This is certainly one way to do it.

In my case, I prefer to use "SUPPLIER_ID-SUPPLIER_PN" for the PRODUCT_ID,
and use the same format for electronictext, contentid, contentassoc, etc.
 It allows me to logically organize the product_id, and also allows me to
blow away a product if I need to.  Example:

     product_id     | product_type_id | primary_product_category_id
 10000-798936838421 | FINISHED_GOOD   | 10003                       |

 10000-798936838421Dru          | ELECTRONIC_TEXT       |
    |                  |                |                  |
                                 | ru            |

The above supplier "10000", and PN (UPC) is the PRODUCT_ID, and the above
ELECTRONIC_TEXT  entry is the russian description (Dru) of the item.

A sample similar to the above was in DemoData, so I went with it.

On Wed, May 1, 2013 at 3:41 AM, Adrian Crum <
[hidden email]> wrote:

>
> On 4/30/2013 4:52 PM, Mike wrote:
>
>> Compare the following three files:
>>
>> framework/entity/fieldtype/**fieldtypemysql.xml
>> framework/entity/fieldtype/**fieldtypepostgres.xml
>> framework/entity/fieldtype/**fieldtypepostnew.xml
>>
>> I bet you are using mysql, or maybe even postgres, which has very LIMITED
>> key value lengths.  In the real world, you never know what key length you
>> may need in the future, so why limit yourself?  Now take a look at
>> "postnew", which uses a generic "text" type for almost all the fields.  It
>> allows ANY key length, which allows the flexibility to create logical
>> relations, even for part numbers that may be 60 characters or so.
>>
>> I learned this the hard way, when I came across long part numbers, and
>> struggled to figure out a way to logically store them.  Back then I was
>> using mysql.
>>
>
> Part numbers are a GOOD IDENTIFICATION. So, you wouldn't put the part
> number in a 20 character ID field. Instead, you would use the standard ID
> sequence generator for the PRODUCT, then link the PRODUCT to a GOOD
> IDENTIFICATION.
>
> -Adrian
>
>

[Mike Z]

Hey Paul.  I don't really think it matters too much any more with modern
database systems with gigabytes of RAM.  Most DB systems the size of ofbiz
run completely in RAM, and although storing an integer as a primary key is
still optimal, even using a UUID as a primary key would still be lighting
fast, as long as the index is completely in RAM.

The above was true in 1999, when a 2U server cost $4000 and came with 512MB
of RAM.


On Wed, May 1, 2013 at 11:48 PM, Paul Foxworthy <[hidden email]> wrote:

> I would also urge caution in changing keys.
>
> There is one advantage in using UUIDs. If you have distributed or
> replicated
> data, you can generate new rows in different locations and you can be sure
> there will be no collisions when you synchronize later on.
>
> There are disadvantages to UUIDs too. Compactness matters in keys. An
> excessive key size means database index entries are big, and therefore less
> will fit in pages and there will be more disk I/O to search the index.
>
> UUIDs are random, so in tree indexes pages will be split at random, and
> there will be considerable fragmentation. In contrast, if the key is
> continually increasing, all the action is in the bottom right hand corner
> of
> a tree index, and (assuming the keys are stable and a low number of
> deletes)
> there won't be many random splits elsewhere.
>
> So UUIDs really only make sense if you have distributed or replicated data,
> and that's not typical for OFBiz.
>
> If you really wanted to use UUIDs, it might be possible to store them in
> binary form, where they would occupy 16 bytes (128 bits).
>
> Cheers
>
> Paul Foxworthy
>
>
> Mike Z wrote
> > This is certainly one way to do it.
> >
> > In my case, I prefer to use "SUPPLIER_ID-SUPPLIER_PN" for the PRODUCT_ID,
> > and use the same format for electronictext, contentid, contentassoc, etc.
> >  It allows me to logically organize the product_id, and also allows me to
> > blow away a product if I need to.  Example:
> >
> >      product_id     | product_type_id | primary_product_category_id
> >  10000-798936838421 | FINISHED_GOOD   | 10003                       |
> >
> >  10000-798936838421Dru          | ELECTRONIC_TEXT       |
> >     |                  |                |                  |
> >                                  | ru            |
> >
> > The above supplier "10000", and PN (UPC) is the PRODUCT_ID, and the above
> > ELECTRONIC_TEXT  entry is the russian description (Dru) of the item.
> >
> > A sample similar to the above was in DemoData, so I went with it.
> >
> > On Wed, May 1, 2013 at 3:41 AM, Adrian Crum <
>
> > adrian.crum@
>
> >> wrote:
> >
> >>
> >> On 4/30/2013 4:52 PM, Mike wrote:
> >>
> >>> Compare the following three files:
> >>>
> >>> framework/entity/fieldtype/**fieldtypemysql.xml
> >>> framework/entity/fieldtype/**fieldtypepostgres.xml
> >>> framework/entity/fieldtype/**fieldtypepostnew.xml
> >>>
> >>> I bet you are using mysql, or maybe even postgres, which has very
> >>> LIMITED
> >>> key value lengths.  In the real world, you never know what key length
> >>> you
> >>> may need in the future, so why limit yourself?  Now take a look at
> >>> "postnew", which uses a generic "text" type for almost all the fields.
> >>> It
> >>> allows ANY key length, which allows the flexibility to create logical
> >>> relations, even for part numbers that may be 60 characters or so.
> >>>
> >>> I learned this the hard way, when I came across long part numbers, and
> >>> struggled to figure out a way to logically store them.  Back then I was
> >>> using mysql.
> >>>
> >>
> >> Part numbers are a GOOD IDENTIFICATION. So, you wouldn't put the part
> >> number in a 20 character ID field. Instead, you would use the standard
> ID
> >> sequence generator for the PRODUCT, then link the PRODUCT to a GOOD
> >> IDENTIFICATION.
> >>
> >> -Adrian
> >>
> >>
>
>
>
>
>
> -----
> --
> Coherent Software Australia Pty Ltd
> http://www.coherentsoftware.com.au/
>
> Bonsai ERP, the all-inclusive ERP system
> http://www.bonsaierp.com.au/
>
> --
> View this message in context:
> http://ofbiz.135035.n4.nabble.com/Use-UUID-for-ProductID-tp4640950p4640971.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>