+1 Thanks Paul,
Michael > Am 06.04.2017 um 19:59 schrieb Paul Mandeltort <[email protected]>: > > While I don’t know the details of your application, sounds like you are > trying to hardcode a join into the ProductID primary key which is just going > to lead you to a world of pain down the road. > > OFbiz supports composite/compound primary keys which in many cases eliminates > the need for an arbitrary unique identifier. > https://en.wikipedia.org/wiki/Compound_key > <https://en.wikipedia.org/wiki/Compound_key> OFbiz uses these extensively in > sub entities. Look in the entity reference (/webtools/control/entityref) > you’ll see the primary keys in red. > > The supplierProduct entity is already setup with a composite primary key > OOTB. > Product.ProductID-PartyID-MOQ-Currency guarantees uniqueness within the > entity. > > Why crap up your Product entity with redundant information that should be > kept in the supplier table? Products should be things that are, to an > end-user, unique. A hardware store, for example, sells framing 2x4’s from > thousands of suppliers, but the same end-user Product number is applied to > them. Under your proposed data import integration, you’ll have to maintain > descriptions, titles, content, pricing, associations, variants, etc FOR EVERY > SUPPLIER. You’ll go insane. > > Need to select, delete, etc all your products from a supplier? Just do the > simple joined query: > > SELECT FROM supplierProduct WHERE > supplierProduct.productId=10000 AND > supplier.productManufacturerCode=798936836182. > > That’s the same. > > supplier.productManufacturerCode is currently not part of the SupplierProduct > composite key but you could make it one if you’d like. > > The 20 character limit is there for a reason - IDs (such as productId) are > used in MANY areas of Ofbiz, including forms like invoices, sales orders, > purchase orders, barcodes, receipts, etc, so having a known sane limit there > enables consistency throughout the business processes. I wouldn’t mess with > it. > > You gotta remember that Ofbiz is far from “finished”, In many areas, > human-readable values are “encoded” into ID fields as a shortcut to avoid > creating lookup entities for that information and to simplify programming for > things that only ever have a handful of values in the entity like with the > roleTypeId=“INTERNAL_ORGANIZATIO” that was mentioned originally, > > As a general rule, if you find yourself trying to change the framework for > your convenience, take a real good hard look as to why you’re doing that. > Every time I try to take shortcuts with the data model because I’m too lazy > to get my data cleaned up to fit it, I ALWAYS regret it,100% of the time, > often years later. > > Hope that helps! > —P > > > >> On Apr 6, 2017, at 12:34 AM, Mike <[email protected]> wrote: >> >> Apologies for the absurd reference. Certainly the screen real estate is >> the biggest issue. Sure, it is nice to display primary keys within 20 >> characters... >> >> But let me give you a working example. In fact, this was a real issue with >> me, considering the import of products. Sure, I could have assigned a >> non-meaningful sequential number, but I like real reference, like a part >> number or UPC code. In my case, multiple suppliers may carry the same UPC, >> so I elected to create the primary key as "SUPPLIER_CODE-UPC". This way I >> can always work on a set of P/Ns from a given supplier. So, the primary >> key becomes: "10000-798936836182", already 18 characters. >> >> <Product productId="10000-798936836182" ... CHR=18 >> <ProductCategoryMember productId="10000-798936836182" >> productCategoryId="10002"... >> <ProductPrice productId="10000-798936836182"... >> <SupplierProduct productId="10000-798936836182"... >> <GoodIdentification productId="10000-798936836182" idValue="798936836182"... >> <ProductFacility productId="10000-798936836182"... >> <DataResource dataResourceId="10000-798936836182Den"... CHR=21 **FAIL** >> <ElectronicText dataResourceId="10000-798936836182Den"... CHR=21 **FAIL** >> <Content dataResourceId="10000-798936836182Den" ... >> <ProductContent productId="10000-798936836182" >> contentId="10000-798936836182Den" ... >> <ContentAssoc contentId="10000-798936836182Den" >> contentIdTo="10000-798936836182Den"/> ... >> <DataResource dataResourceTypeId="ELECTRONIC_TEXT" >> dataResourceId="10000-798936836182Len" ... >> <ElectronicText dataResourceId="10000-798936836182Len" ... >> <Content dataResourceId="10000-798936836182Len" >> contentId="10000-798936836182Len" ... >> <ContentAssoc contentId="10000-798936836182Len" ... >> ...etc... >> >> And this is a relatively short part number sequence. If I WANT to pad >> extra info into the primary key.. for MY convenience, I don't have to worry >> about the import failing due to a 20 character limit somewhere. >> >> In addition, setting up the data import as above allows me to quickly blow >> away the product, because all primary keys on all affected tables were >> created using a consistent pattern. >> >> That is all that I am saying. Once you set up a database, you have to to >> live with it. >> >> On Wed, Apr 5, 2017 at 3:12 PM, Scott Gray <[email protected]> >> wrote: >> >>> Perhaps lookup performance isn't the only consideration? >>> >>> A few things come to mind: >>> - screen realestate when PKs need to be displayed >>> - bandwidth for syncing to slaves and transporting data to/from the client >>> - file size for export/import be it XML or whatever >>> >>> Given that PKs shouldn't perform any function beyond guaranteeing >>> uniqueness within a given table, and that we use numeric sequences for >>> nonstatic tables, I struggle to see where it makes sense to use anything >>> bigger than 20 characters. So we have to abbreviate some seed data to fit, >>> not really a big deal and certainly not "absurd". >>> >>> Like any other code base in the world, OFBiz contains opinionated design. >>> Everyone is free to discuss those opinions ad nauseam, but using strong >>> language such as "absurd" because you have a different opinion is >>> unnecessary and not constructive to the conversation. >>> >>> Regards >>> Scott >>> >>> >>> >>>> On 6/04/2017 09:33, "Mike" <[email protected]> wrote: >>>> >>>> Well, with postgresql, and localpostnew, there are no worries about UTF8 >>>> compatibility, or lengths of *ANY* fields. It works just fine, and the >>>> performance is fast. >>>> >>>> One may argue that you SHOULD limit your primary ID fields. OK: Maybe >>> to >>>> 255, using VARYING(255)... But never use VARCHAR(255), because you are >>>> physically storing 255 characters... but never just 20. >>>> >>>> On Wed, Apr 5, 2017 at 12:42 PM, Jacques Le Roux < >>>> [email protected]> wrote: >>>> >>>>> For history sake: I committed localpostnew. >>>>> >>>>> After a discussion (on dev ML or somewhere else? Unfortunately I can't >>>>> find) it was commonly agreed that we should merge localpostnew in >>>>> localpostgres and then remove localpostnew. >>>>> >>>>> Later we commonly decided http://markmail.org/message/op2yl3pcbj3lgxpg >>>> to >>>>> revert some changes in the new (merged) localpostgres >>>>> >>>>> Feel free to use localpostnew. We could even put it back in, as >>> suggested >>>>> Nicolas, but I believe it should be then named otherwise to avoid >>>> confusion >>>>> >>>>> Jacques >>>>> >>>>> >>>>>> Le 05/04/2017 à 19:32, Mike a écrit : >>>>>> >>>>>> Pierre, here is an example from the demo data: >>>>>> >>>>>> accounting_OrganizationData.xml: <PartyRole partyId="RECEIVING" >>>>>> roleTypeId="INTERNAL_ORGANIZATIO"/> >>>>>> >>>>>> The default of ID (20 chrs) is so small that you can't even properly >>>> spell >>>>>> "INTERNAL_ORGANIZATION"... I work with databases every day, and I >>> would >>>> be >>>>>> so limited if I had to work with such small primary IDs. >>>>>> >>>>>> The thing is you don't want to not limit yourself when you first >>> build a >>>>>> database. The jira is interesting, and GUIDs are a good example. >>>>>> >>>>>> Personally, I use postgresql, using the "localpostnew" type... Removed >>>>>> from >>>>>> trunk for some reason.. It has unlimited primary ID sizes (ok, 2.1G), >>>>>> which >>>>>> allows me to create any sort of primary key I want. >>>>>> >>>>>> <field-type-def type="id" sql-type="TEXT" >>>>>> java-type="String"/> >>>>>> <field-type-def type="id-long" sql-type="TEXT" >>>> java-type="String"/> >>>>>> <field-type-def type="id-vlong" sql-type="TEXT" >>>> java-type="String"/> >>>>>> >>>>>> If you think that type=TEXT is slow or less efficient.. Here is what >>>>>> postgres says about type "TEXT".. >>>>>> >>>>>> https://www.postgresql.org/docs/9.3/static/datatype-character.html >>>>>> >>>>>> *Tip:* There is no performance difference among these three types, >>> apart >>>>>> >>>>>> from increased storage space when using the blank-padded type, and a >>> few >>>>>> extra CPU cycles to check the length when storing into a >>>>>> length-constrained >>>>>> column. While character(n) has performance advantages in some other >>>>>> database systems, there is no such advantage inPostgreSQL; in fact >>>>>> character(n) is usually the slowest of the three because of its >>>> additional >>>>>> storage costs. In most situations text or character varying should be >>>> used >>>>>> instead. >>>>>> >>>>>> Mysql has a similar type... I personally haven't tested it. >>>>>> >>>>>> On Wed, Apr 5, 2017 at 10:06 AM, Pierre Smits <[email protected] >>>> >>>>>> wrote: >>>>>> >>>>>> HI Mike, all, >>>>>>> >>>>>>> Re 2: Talk about adjustment of default key size >>>>>>> Why is that absurd? You believe it is too long/too short? >>>>>>> Following JIRA issue may be of interest: >>>>>>> https://issues.apache.org/jira/browse/OFBIZ-8343 >>>>>>> >>>>>>> Best regards, >>>>>>> >>>>>>> Pierre Smits >>>>>>> >>>>>>> ORRTIZ.COM <http://www.orrtiz.com> >>>>>>> OFBiz based solutions & services >>>>>>> >>>>>>> OFBiz Extensions Marketplace >>>>>>> http://oem.ofbizci.net/oci-2/ >>>>>>> >>>>>>> On Wed, Apr 5, 2017 at 5:10 PM, Mike <[email protected]> wrote: >>>>>>> >>>>>>> Nice videos. Regarding the mysql setup, you may want to include two >>>>>>>> >>>>>>> items: >>>>>>> >>>>>>>> 1) Make sure mysql is setup as UTF8, discussed earlier in this mail >>>>>>>> >>>>>>> group. >>>>>>> >>>>>>>> Requires tweaking: >>>>>>>> >>>>>>>> framework/entity/config/entityengine.xml >>>>>>>> /etc/mysql/my.cnf >>>>>>>> >>>>>>>> 2) Talk about adjusting the default sizes of primary keys (ID). The >>>>>>>> default is an absurd 20 characters: >>>>>>>> >>>>>>>> framework/entity/fieldtype/fieldtypemysql.xml >>>>>>>> >>>>>>>> <field-type-def type="id" sql-type="VARCHAR(20)" >>>>>>>> java-type="String"/> >>>>>>>> <field-type-def type="id-long" sql-type="VARCHAR(60)" >>>>>>>> java-type="String"/> >>>>>>>> <field-type-def type="id-vlong" sql-type="VARCHAR(250)" >>>>>>>> java-type="String"/> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Wed, Apr 5, 2017 at 6:03 AM, Pranay Pandey < >>>>>>>> [email protected]> wrote: >>>>>>>> >>>>>>>> Thanks so much Deepak! >>>>>>>>> >>>>>>>>> Best regards, >>>>>>>>> >>>>>>>>> Pranay Pandey >>>>>>>>> >>>>>>>>> >>>>>>>>> On Wed, Apr 5, 2017 at 5:51 PM, Deepak Dixit >>>>>>>>> >>>>>>>> <deepak.dixit@hotwaxsystems. >>>>>>> >>>>>>>> com >>>>>>>>> >>>>>>>>>> wrote: >>>>>>>>>> Hi Team, >>>>>>>>>> >>>>>>>>>> Here are some more videos from Pranay >>>>>>>>>> >>>>>>>>>> - Setup OFBiz in IntelliJ IDEA IDE - Release 16.11 and Trunk >>>>>>>>>> <https://youtu.be/mxToh2rX7NY> >>>>>>>>>> - Setup OFBiz with MySQL <https://youtu.be/Lzmv0DCC5N4> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Thanks Pranay for your effort. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Thanks & Regards >>>>>>>>>> -- >>>>>>>>>> Deepak Dixit >>>>>>>>>> www.hotwaxsystems.com >>>>>>>>>> >>>>>>>>>> On Wed, Mar 22, 2017 at 7:07 PM, akash jain < >>> [email protected] >>>>> >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>> Nice videos, thanks Pranay! >>>>>>>>>>> >>>>>>>>>>> Thanks and Regards >>>>>>>>>>> -- >>>>>>>>>>> Akash Jain >>>>>>>>>>> >>>>>>>>>>> On Thu, Mar 9, 2017 at 6:18 PM, Deepak Dixit >>>>>>>>>>> >>>>>>>>>> <deepak.dixit@hotwaxsystems. >>>>>>>>> >>>>>>>>>> com >>>>>>>>>>> >>>>>>>>>>>> wrote: >>>>>>>>>>>> Hi Everyone, >>>>>>>>>>>> >>>>>>>>>>>> Pranay has created two video tutorials, these have been >>> published >>>>>>>>>>>> >>>>>>>>>>> on >>>>>>>> >>>>>>>>> our >>>>>>>>>> >>>>>>>>>>> OFBiz YouTube channel <https://www.youtube.com/user/ofbiz>: >>>>>>>>>>>> 1 - Apache OFBiz Mailing Lists <https://www.youtube.com/ >>>>>>>>>>>> watch?v=bIS2kftvsq4> >>>>>>>>>>>> 2 - OFBiz Beginners Tutorial - Basic Setup Release16.11 >>>>>>>>>>>> <https://youtu.be/efkB_aN-ODw> >>>>>>>>>>>> >>>>>>>>>>>> Thanks Pranay for these helpful videos. >>>>>>>>>>>> >>>>>>>>>>>> Thanks & Regards >>>>>>>>>>>> -- >>>>>>>>>>>> Deepak Dixit >>>>>>>>>>>> www.hotwaxsystems.com >>>>>>>>>>>> >>>>>>>>>>>> >>>>> >>>> >>> >
smime.p7s
Description: S/MIME cryptographic signature
