https://cwiki.apache.org/confluence/display/OFBIZ/Data+Model+Diagrams <https://cwiki.apache.org/confluence/display/OFBIZ/Data+Model+Diagrams> Print these up and frame them, I wish I had found them earlier in my OFbiz journey. Will quickly show you how everything fits together. My greatest regret was not finding them sooner. They’re a little dated but still 95% accurate especially for getting going.
Good place to store UPC information is here: https://demo-stable.ofbiz.apache.org/catalog/control/EditProductGoodIdentifications?productId=GZ-1000 <https://demo-stable.ofbiz.apache.org/catalog/control/EditProductGoodIdentifications?productId=GZ-1000> Then you can use an arbitrary schema for your top-level product catalog which is always a good idea. I suggest avoiding modifying the core data model if at all possible to protect yourself down the road for upgrades. It’s pretty easy to paint yourself into a corner if you start modifying that stuff unnecessarily. —P > On Apr 6, 2017, at 4:01 PM, Mike <[email protected]> wrote: > >> 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 <[email protected]> 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 <[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 >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>> >>>>> >>>> >> >>
