> 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.
Awesome advice Paul, and a great example. It's definitely better to just use the UPC (in my case), and maintain the supplier table. For stuff that *I* control (like products, descriptions, content, electronictext, etc), I prefer to use primary keys that I control. For all other stuff (sales orders, invoices, etc.) ofbiz can take care of that. Thanks a lot. On Thu, Apr 6, 2017 at 10:59 AM, Paul Mandeltort <p...@marcospec.com> wrote: > 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 <mz4whee...@gmail.com> 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 <scott.g...@hotwaxsystems.com > > > > 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" <mz4whee...@gmail.com> 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 < > >>> jacques.le.r...@les7arts.com> 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 < > pierre.sm...@gmail.com > >>> > >>>>> 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 <mz4whee...@gmail.com> 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 < > >>>>>>> pranay.pan...@hotwaxsystems.com> 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 < > >> akash1.ja...@gmail.com > >>>> > >>>>>>>>> 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 > >>>>>>>>>>> > >>>>>>>>>>> > >>>> > >>> > >> > >
