Why primary keys are VAR_CHAR type in OFBiz?
Locked
Why primary keys are VAR_CHAR type in OFBiz?
 
|
Hello Frns,
I am a *newbie* in OFBiz and was playing with the OFBiz. During my play I found that in OFBiz data type for all the primary keys are VAR-CHAR, but VAR-CHAR data type is not a good practice for the efficiency perspective because it takes extra time during searching. Can anyone please tell me why OFBiz uses VAR-CHAR data type used for primary key instead of NUMERIC data type. Any help would be greatly appreciated. Dhruv. |
Re: Why primary keys are VAR_CHAR type in OFBiz?
|
|
1. The performance overhead is minimal on modern database systems. 2. Character strings give significant flexibility, especially when it is common to migrate data from other systems that have characters in their IDs. 3. Being able to prefix IDs is used in a number of places in OFBiz, including prefixes for entire systems when data transfers between them are done to separate ID spaces. Welcome to the real world! ;) -David On Feb 17, 2009, at 8:05 AM, Dhruv Datta wrote: > Hello Frns, > > I am a *newbie* in OFBiz and was playing with the OFBiz. > > During my play I found that in OFBiz data type for all the primary > keys > are VAR-CHAR, but VAR-CHAR data type is not a good practice for the > efficiency perspective because it takes extra time during searching. > Can > anyone please tell me why OFBiz uses VAR-CHAR data type used for > primary key > instead of NUMERIC data type. > > Any help would be greatly appreciated. > > > Dhruv. |
Re: Why primary keys are VAR_CHAR type in OFBiz?
Administrator
|
2d or 3d time this question is asked : time for a FAQ
http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo#FAQ-Tips-Tricks-Cookbook-HowTo-WhyprimarykeysareVARCHARtypeinOFBiz Thanks David Jacques From: "David E Jones" <[hidden email]> > > 1. The performance overhead is minimal on modern database systems. > 2. Character strings give significant flexibility, especially when it is common to migrate data from other systems that have > characters in their IDs. > 3. Being able to prefix IDs is used in a number of places in OFBiz, including prefixes for entire systems when data transfers > between them are done to separate ID spaces. > > Welcome to the real world! ;) > > -David > > > On Feb 17, 2009, at 8:05 AM, Dhruv Datta wrote: > >> Hello Frns, >> >> I am a *newbie* in OFBiz and was playing with the OFBiz. >> >> During my play I found that in OFBiz data type for all the primary keys >> are VAR-CHAR, but VAR-CHAR data type is not a good practice for the >> efficiency perspective because it takes extra time during searching. Can >> anyone please tell me why OFBiz uses VAR-CHAR data type used for primary key >> instead of NUMERIC data type. >> >> Any help would be greatly appreciated. >> >> >> Dhruv. > |
Re: Why primary keys are VAR_CHAR type in OFBiz?
|
|
Sorry guys, but I don't agree on the first point. An Integer or Number as
primary will always win the time race compared to a varchar, specially if there's a group or order clause included. I do agree about the flexibility (that also comes with other complexities), but the performance issue is unfortunately not that simple. Of course, it's also a matter of how big your database is. The bigger the worse. Regards, Sven 2009/2/17 Jacques Le Roux <[hidden email]> > 2d or 3d time this question is asked : time for a FAQ > > http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo#FAQ-Tips-Tricks-Cookbook-HowTo-WhyprimarykeysareVARCHARtypeinOFBiz > > Thanks David > > Jacques > > From: "David E Jones" <[hidden email]> > > >> 1. The performance overhead is minimal on modern database systems. >> 2. Character strings give significant flexibility, especially when it is >> common to migrate data from other systems that have >> characters in their IDs. >> 3. Being able to prefix IDs is used in a number of places in OFBiz, >> including prefixes for entire systems when data transfers >> between them are done to separate ID spaces. >> >> Welcome to the real world! ;) >> >> -David >> >> >> On Feb 17, 2009, at 8:05 AM, Dhruv Datta wrote: >> >> Hello Frns, >>> >>> I am a *newbie* in OFBiz and was playing with the OFBiz. >>> >>> During my play I found that in OFBiz data type for all the primary keys >>> are VAR-CHAR, but VAR-CHAR data type is not a good practice for the >>> efficiency perspective because it takes extra time during searching. Can >>> anyone please tell me why OFBiz uses VAR-CHAR data type used for primary >>> key >>> instead of NUMERIC data type. >>> >>> Any help would be greatly appreciated. >>> >>> >>> Dhruv. >>> >> >> > > |
Re: Why primary keys are VAR_CHAR type in OFBiz?
|
|
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 there was not statement on comparison, you are correct. have you done a test though to see what the real difference in msec is, on what you define as a large database. Sven Wesley sent the following on 2/17/2009 11:28 AM: > Sorry guys, but I don't agree on the first point. An Integer or Number as > primary will always win the time race compared to a varchar, specially if > there's a group or order clause included. I do agree about the flexibility > (that also comes with other complexities), but the performance issue is > unfortunately not that simple. Of course, it's also a matter of how big your > database is. The bigger the worse. > > Regards, > Sven > > > 2009/2/17 Jacques Le Roux <[hidden email]> > >> 2d or 3d time this question is asked : time for a FAQ >> >> http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo#FAQ-Tips-Tricks-Cookbook-HowTo-WhyprimarykeysareVARCHARtypeinOFBiz >> >> Thanks David >> >> Jacques >> >> From: "David E Jones" <[hidden email]> >> >> >>> 1. The performance overhead is minimal on modern database systems. >>> 2. Character strings give significant flexibility, especially when it is >>> common to migrate data from other systems that have >>> characters in their IDs. >>> 3. Being able to prefix IDs is used in a number of places in OFBiz, >>> including prefixes for entire systems when data transfers >>> between them are done to separate ID spaces. >>> >>> Welcome to the real world! ;) >>> >>> -David >>> >>> >>> On Feb 17, 2009, at 8:05 AM, Dhruv Datta wrote: >>> >>> Hello Frns, >>>> I am a *newbie* in OFBiz and was playing with the OFBiz. >>>> >>>> During my play I found that in OFBiz data type for all the primary keys >>>> are VAR-CHAR, but VAR-CHAR data type is not a good practice for the >>>> efficiency perspective because it takes extra time during searching. Can >>>> anyone please tell me why OFBiz uses VAR-CHAR data type used for primary >>>> key >>>> instead of NUMERIC data type. >>>> >>>> Any help would be greatly appreciated. >>>> >>>> >>>> Dhruv. >>>> >>> >> > Version: GnuPG v1.4.6 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJmxQ6rP3NbaWWqE4RAkfjAKClPAs2o0o58AdYXTNWxsxK/+tAQgCePrLa Z7El/e8xhPwsbThwrEuo8zU= =58Or -----END PGP SIGNATURE----- |
Re: Why primary keys are VAR_CHAR type in OFBiz?
|
|
In reply to this post by Sven Wesley
Again, that depends upon the database, the operating system, and the
hardware. If you have an installation that performs better with a different field type, then by all means, change your local copy to use a different field type. -Adrian Sven Wesley wrote: > Sorry guys, but I don't agree on the first point. An Integer or Number as > primary will always win the time race compared to a varchar, specially if > there's a group or order clause included. I do agree about the flexibility > (that also comes with other complexities), but the performance issue is > unfortunately not that simple. Of course, it's also a matter of how big your > database is. The bigger the worse. > > Regards, > Sven > > > 2009/2/17 Jacques Le Roux <[hidden email]> > >> 2d or 3d time this question is asked : time for a FAQ >> >> http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo#FAQ-Tips-Tricks-Cookbook-HowTo-WhyprimarykeysareVARCHARtypeinOFBiz >> >> Thanks David >> >> Jacques >> >> From: "David E Jones" <[hidden email]> >> >> >>> 1. The performance overhead is minimal on modern database systems. >>> 2. Character strings give significant flexibility, especially when it is >>> common to migrate data from other systems that have >>> characters in their IDs. >>> 3. Being able to prefix IDs is used in a number of places in OFBiz, >>> including prefixes for entire systems when data transfers >>> between them are done to separate ID spaces. >>> >>> Welcome to the real world! ;) >>> >>> -David >>> >>> >>> On Feb 17, 2009, at 8:05 AM, Dhruv Datta wrote: >>> >>> Hello Frns, >>>> I am a *newbie* in OFBiz and was playing with the OFBiz. >>>> >>>> During my play I found that in OFBiz data type for all the primary keys >>>> are VAR-CHAR, but VAR-CHAR data type is not a good practice for the >>>> efficiency perspective because it takes extra time during searching. Can >>>> anyone please tell me why OFBiz uses VAR-CHAR data type used for primary >>>> key >>>> instead of NUMERIC data type. >>>> >>>> Any help would be greatly appreciated. >>>> >>>> >>>> Dhruv. >>>> >>> >> > |
Re: Why primary keys are VAR_CHAR type in OFBiz?
|
Administrator
|
In reply to this post by BJ Freeman
EnterpriseDB guys know what they are talking about (they are offering Postgres commercial support)
http://markmail.org/message/7ykdxmvc7fvonx5q Jacques PS : OK, I'm a Postgres fan Jacques From: "BJ Freeman" <[hidden email]> > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > there was not statement on comparison, you are correct. > have you done a test though to see what the real difference in msec is, > on what you define as a large database. > > > > Sven Wesley sent the following on 2/17/2009 11:28 AM: >> Sorry guys, but I don't agree on the first point. An Integer or Number as >> primary will always win the time race compared to a varchar, specially if >> there's a group or order clause included. I do agree about the flexibility >> (that also comes with other complexities), but the performance issue is >> unfortunately not that simple. Of course, it's also a matter of how big your >> database is. The bigger the worse. >> >> Regards, >> Sven >> >> >> 2009/2/17 Jacques Le Roux <[hidden email]> >> >>> 2d or 3d time this question is asked : time for a FAQ >>> >>> http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo#FAQ-Tips-Tricks-Cookbook-HowTo-WhyprimarykeysareVARCHARtypeinOFBiz >>> >>> Thanks David >>> >>> Jacques >>> >>> From: "David E Jones" <[hidden email]> >>> >>> >>>> 1. The performance overhead is minimal on modern database systems. >>>> 2. Character strings give significant flexibility, especially when it is >>>> common to migrate data from other systems that have >>>> characters in their IDs. >>>> 3. Being able to prefix IDs is used in a number of places in OFBiz, >>>> including prefixes for entire systems when data transfers >>>> between them are done to separate ID spaces. >>>> >>>> Welcome to the real world! ;) >>>> >>>> -David >>>> >>>> >>>> On Feb 17, 2009, at 8:05 AM, Dhruv Datta wrote: >>>> >>>> Hello Frns, >>>>> I am a *newbie* in OFBiz and was playing with the OFBiz. >>>>> >>>>> During my play I found that in OFBiz data type for all the primary keys >>>>> are VAR-CHAR, but VAR-CHAR data type is not a good practice for the >>>>> efficiency perspective because it takes extra time during searching. Can >>>>> anyone please tell me why OFBiz uses VAR-CHAR data type used for primary >>>>> key >>>>> instead of NUMERIC data type. >>>>> >>>>> Any help would be greatly appreciated. >>>>> >>>>> >>>>> Dhruv. >>>>> >>>> >>> >> > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (MingW32) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFJmxQ6rP3NbaWWqE4RAkfjAKClPAs2o0o58AdYXTNWxsxK/+tAQgCePrLa > Z7El/e8xhPwsbThwrEuo8zU= > =58Or > -----END PGP SIGNATURE----- > |
Re: Why primary keys are VAR_CHAR type in OFBiz?
|
|
Uggh.... That post mentions this: "What you *should* be thinking about is do you really want a varchar PK? Generally speaking, it's better to use a phantom PK (ie: a SERIAL), and put a UNIQUE constraint on the varchar in the appropriate table." IMO that is nothing short of horrible database design and complicates data models and applications significantly. There is lots of good literature showing a better way (ie natural keys), like Joe Celko who has been mentioned on this list a few times. Of course, as with any practice there are those for and against it. In absence of requirements and objectives to guide you, the only answer is it doesn't matter, you can go sit in the sun for all it matters because you haven't decided what you want to do. Once details are in place, then you can decide. -David On Feb 17, 2009, at 1:17 PM, Jacques Le Roux wrote: > EnterpriseDB guys know what they are talking about (they are > offering Postgres commercial support) > http://markmail.org/message/7ykdxmvc7fvonx5q > > Jacques > PS : OK, I'm a Postgres fan > > Jacques > > From: "BJ Freeman" <[hidden email]> >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> there was not statement on comparison, you are correct. >> have you done a test though to see what the real difference in msec >> is, >> on what you define as a large database. >> >> >> >> Sven Wesley sent the following on 2/17/2009 11:28 AM: >>> Sorry guys, but I don't agree on the first point. An Integer or >>> Number as >>> primary will always win the time race compared to a varchar, >>> specially if >>> there's a group or order clause included. I do agree about the >>> flexibility >>> (that also comes with other complexities), but the performance >>> issue is >>> unfortunately not that simple. Of course, it's also a matter of >>> how big your >>> database is. The bigger the worse. >>> >>> Regards, >>> Sven >>> >>> >>> 2009/2/17 Jacques Le Roux <[hidden email]> >>> >>>> 2d or 3d time this question is asked : time for a FAQ >>>> >>>> http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo#FAQ-Tips-Tricks-Cookbook-HowTo-WhyprimarykeysareVARCHARtypeinOFBiz >>>> >>>> Thanks David >>>> >>>> Jacques >>>> >>>> From: "David E Jones" <[hidden email]> >>>> >>>> >>>>> 1. The performance overhead is minimal on modern database systems. >>>>> 2. Character strings give significant flexibility, especially >>>>> when it is >>>>> common to migrate data from other systems that have >>>>> characters in their IDs. >>>>> 3. Being able to prefix IDs is used in a number of places in >>>>> OFBiz, >>>>> including prefixes for entire systems when data transfers >>>>> between them are done to separate ID spaces. >>>>> >>>>> Welcome to the real world! ;) >>>>> >>>>> -David >>>>> >>>>> >>>>> On Feb 17, 2009, at 8:05 AM, Dhruv Datta wrote: >>>>> >>>>> Hello Frns, >>>>>> I am a *newbie* in OFBiz and was playing with the OFBiz. >>>>>> >>>>>> During my play I found that in OFBiz data type for all the >>>>>> primary keys >>>>>> are VAR-CHAR, but VAR-CHAR data type is not a good practice for >>>>>> the >>>>>> efficiency perspective because it takes extra time during >>>>>> searching. Can >>>>>> anyone please tell me why OFBiz uses VAR-CHAR data type used >>>>>> for primary >>>>>> key >>>>>> instead of NUMERIC data type. >>>>>> >>>>>> Any help would be greatly appreciated. >>>>>> >>>>>> >>>>>> Dhruv. >>>>>> >>>>> >>>> >>> >> -----BEGIN PGP SIGNATURE----- >> Version: GnuPG v1.4.6 (MingW32) >> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org >> >> iD8DBQFJmxQ6rP3NbaWWqE4RAkfjAKClPAs2o0o58AdYXTNWxsxK/+tAQgCePrLa >> Z7El/e8xhPwsbThwrEuo8zU= >> =58Or >> -----END PGP SIGNATURE----- > > |
Re: Why primary keys are VAR_CHAR type in OFBiz?
|
Administrator
|
I mostly retained <<Do you *really* need to worry about the extra overhead of varchar vs int? Probably not.>>
Jacques From: "David E Jones" <[hidden email]> > > Uggh.... > > That post mentions this: "What you *should* be thinking about is do you really want a varchar PK? Generally speaking, it's better > to use a phantom PK (ie: a SERIAL), and put a UNIQUE constraint on the varchar in the appropriate table." > > IMO that is nothing short of horrible database design and complicates data models and applications significantly. There is lots > of good literature showing a better way (ie natural keys), like Joe Celko who has been mentioned on this list a few times. > > Of course, as with any practice there are those for and against it. > > In absence of requirements and objectives to guide you, the only answer is it doesn't matter, you can go sit in the sun for all > it matters because you haven't decided what you want to do. Once details are in place, then you can decide. > > -David > > > On Feb 17, 2009, at 1:17 PM, Jacques Le Roux wrote: > >> EnterpriseDB guys know what they are talking about (they are offering Postgres commercial support) >> http://markmail.org/message/7ykdxmvc7fvonx5q >> >> Jacques >> PS : OK, I'm a Postgres fan >> >> Jacques >> >> From: "BJ Freeman" <[hidden email]> >>> -----BEGIN PGP SIGNED MESSAGE----- >>> Hash: SHA1 >>> >>> there was not statement on comparison, you are correct. >>> have you done a test though to see what the real difference in msec is, >>> on what you define as a large database. >>> >>> >>> >>> Sven Wesley sent the following on 2/17/2009 11:28 AM: >>>> Sorry guys, but I don't agree on the first point. An Integer or Number as >>>> primary will always win the time race compared to a varchar, specially if >>>> there's a group or order clause included. I do agree about the flexibility >>>> (that also comes with other complexities), but the performance issue is >>>> unfortunately not that simple. Of course, it's also a matter of how big your >>>> database is. The bigger the worse. >>>> >>>> Regards, >>>> Sven >>>> >>>> >>>> 2009/2/17 Jacques Le Roux <[hidden email]> >>>> >>>>> 2d or 3d time this question is asked : time for a FAQ >>>>> >>>>> http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo#FAQ-Tips-Tricks-Cookbook-HowTo-WhyprimarykeysareVARCHARtypeinOFBiz >>>>> >>>>> Thanks David >>>>> >>>>> Jacques >>>>> >>>>> From: "David E Jones" <[hidden email]> >>>>> >>>>> >>>>>> 1. The performance overhead is minimal on modern database systems. >>>>>> 2. Character strings give significant flexibility, especially when it is >>>>>> common to migrate data from other systems that have >>>>>> characters in their IDs. >>>>>> 3. Being able to prefix IDs is used in a number of places in OFBiz, >>>>>> including prefixes for entire systems when data transfers >>>>>> between them are done to separate ID spaces. >>>>>> >>>>>> Welcome to the real world! ;) >>>>>> >>>>>> -David >>>>>> >>>>>> >>>>>> On Feb 17, 2009, at 8:05 AM, Dhruv Datta wrote: >>>>>> >>>>>> Hello Frns, >>>>>>> I am a *newbie* in OFBiz and was playing with the OFBiz. >>>>>>> >>>>>>> During my play I found that in OFBiz data type for all the primary keys >>>>>>> are VAR-CHAR, but VAR-CHAR data type is not a good practice for the >>>>>>> efficiency perspective because it takes extra time during searching. Can >>>>>>> anyone please tell me why OFBiz uses VAR-CHAR data type used for primary >>>>>>> key >>>>>>> instead of NUMERIC data type. >>>>>>> >>>>>>> Any help would be greatly appreciated. >>>>>>> >>>>>>> >>>>>>> Dhruv. >>>>>>> >>>>>> >>>>> >>>> >>> -----BEGIN PGP SIGNATURE----- >>> Version: GnuPG v1.4.6 (MingW32) >>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org >>> >>> iD8DBQFJmxQ6rP3NbaWWqE4RAkfjAKClPAs2o0o58AdYXTNWxsxK/+tAQgCePrLa >>> Z7El/e8xhPwsbThwrEuo8zU= >>> =58Or >>> -----END PGP SIGNATURE----- >> >> > |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |