Hi David, Many thanks for your comments.
My responses inline.... Cheers Paul Foxworthy On Apr 2, 2011, at 5:41 AM, Paul Foxworthy wrote: > > > Status History > > --------------------- > > > > I can see the sense in having one repository for all change tracking. We > > just need to bear in mind there are more use cases for using the > information > > in the repository than “auditing”. Auditing is a security activity, and > it > > will be security professionals looking at a history of who did what. > Looking > > at a status history could well be a business activity with no security > > dimension to it. So viewing the history of changes to an order’s status > > might be something a manager would do. > > > > We would need to structure permissions so that people who need some of > the > > information for business purposes can use it even though they are not > > security professionals. The other thing to observe is that status history > is > > currently *on* by default in Ofbiz, and auditing is *off* by default. > > The "audit log" is a pretty general term and does not imply restricted > access to the information. Control of access to any database information is > up to the application developer, and nothing about the term "audit log" > prevents that. > Cool. Do you think it would be a good idea to have limited auditing enabled from the beginning in a demo/starter system, the rough equivalent of the current status history? > > QuantityBreak > > --------------------- > > > > No, don’t get rid of the QuantityBreak entity, and it should be used as > part > > of pricing. Many organisations will have standard QuantityBreaks that > apply > > to a wide range of products. An organization might want to offer various > > agreed prices to customers, but use the same quantity breaks > consistently. > > It should be possible to add a new price for a product without having to > > define quantity breaks, with the risk that the newly entered quantity > breaks > > are inconsistent with standard organisation policy. > > Maybe this wasn't clear... the goal is to get rid of the QuantityBreak > entity and use the quantity fields directly. This significantly simplifies > both the data model and the code. In theory what you say is correct, that > quantity breaks could be maintained separately, but that hasn't happened in > any of the OFBiz UIs, and in fact I've never seen anything like that > actually used (I've seen them built, but then abandoned because for the user > they are cumbersome). > > If an organization wants a policy of specific quantity breaks they can > certainly implement that and design the user interface for it, even with the > data model designed this way. > QuantityBreak is also used for shipping, and quantity breaks for shipping are often in terms of weight. I think that's sensible, and the entity should not be removed altogether. For product pricing as opposed to shipping, how about keeping QuantityBreak, but only to support UIs? Remove the relationship between QuantityBreak and product pricing. What does everyone think? > Purchase and Sales Agreements > > ----------------------------------------------- > > > > - the AgreementItem has the currency for prices. The currency should be > on > > individual AgreementProductAppls. It should be possible to have agreed > > prices in various currencies for a product > > > - AgreementProductAppl should have its own from and thru dates. A > particular > > price might have a different timespan than the Agreement or (in the case > of > > a purchase agreement) the SupplierProduct. You could imagine an agreement > > spanning a year with a list of special prices, each of which is available > > for one month > > This is a good point, it would be good to have prices in multiple > currencies in the same agreement, I'm modifying the AgreementItemProduct > entity in the Mantle data model to support that and in general be more like > the ProductPrice entity. > > On second thought, after looking at the model, I like them (currency, > from/thru dates) on the AgreementItem better since it can apply to multiple > things and agreements may be better organized if price lists for different > currencies and date ranges are under different items (and the actual data is > a lot cleaner). > I think, as a general proposition, a price should have a currency. It's meaningless without. AgreementItem is an organising principle for components of an overall agreement, and it should be up to the user to define agreement items in whatever way makes sense to them. If a company using Ofbiz prices their products in many different currencies, what you've suggested fragments product prices into many different agreement items. The currency that a product happens to be priced in is not necessarily the most important organising principle for agreed prices. Similarly, from and thru dates might be useful on individual AgreementProductAppls. Think about the situation where we have an agreement for a year with a special-of the-month, a special agreed price for a different product each month. Does that on its own justify twelve AgreementItems? I have no problem with an AgreementItem having *default* from date, thru date and currency. When we enter an AgreementProductAppl for the AgreementItem, the defaults obtained from the AgreementItem should be visible, but they should be editable. > > - There’s no way at present to have a quantity break associated with an > > agreed price for a sales agreement. Quantity breaks are currently within > the > > SupplierProduct entity, so are available for purchase agreements, but not > > sales agreements. > > Actually, that's not quite true. The current AgreementProductAppl model is > cumbersome, but this last point IS supported (ie agreement-based sales > pricing with quantity breaks) in both the OFBiz data model and the sales > order code. > >From what I've found: - At https://fisheye6.atlassian.com/browse/ofbiz/trunk/applications/product/src/org/ofbiz/product/price/PriceServices.java?hb=true#to247, I think the product pricing code just looks for the first AgreementProductAppl with no regard for quantities. - As far as I can tell the QuantityBreak entity is only currently used for shipping calculations, not pricing. - The ProductPriceRule entity can hold a number of ProductPriceCond conditions, including a test for the party and for the quantity, but ProductPriceRules aren't related to Agreements, and they are only used for sales, not purchases. I assume you're talking about the QuantityBreak entity being used for agreement-based sales pricing. Can you point me to where in the code that happens? In the data model I'm working on this is simplified and much more clear. See > the "AgreementItemProduct" entity here: > > > https://github.com/jonesde/mantle/blob/master/mantle-udm/entity/PartyEntities.xml > > > > SupplierProduct > > ----------------------- > > > > - As David says, a five-attribute primary key is unwieldy. It should be > > replaced with a simple ID. > > Agreed. Here is my current take on the SupplierProduct entity: > > > https://github.com/jonesde/mantle/blob/master/mantle-udm/entity/ProductInventoryEntities.xml > > > - SupplierProduct has a minimumQuantity attribute, so must be read in > > combination with other SupplierProducts. SupplierProduct should *not* > have > > any information on quantity breaks. There should be just one instance of > > SupplierProduct to record that a product can be obtained from a supplier. > A > > SupplierProduct should have associated QuantityBreaks and ProductPrices, > as > > described in the Data Model Resource Book. > > - lastPrice in SupplierProduct is used both for the last price we > obtained > > the product, and an agreed price. These are two separate things. Given > the > > lastPrice might have been determined by an agreement that has expired, > and > > by a quantity that we are not ordering this time, it's not very useful. > It > > ought to be possible to determine the most recent prices we paid at > various > > quantity breaks for a given product from a given supplier > > Actually in OFBiz the "lastPrice" is meant to be just that, the last known > price from the supplier. It isn't meant to represent any sort of purchase > agreement. The Agreement* entities should be used for that (though I don't > know off the top of my head if that is current supported in the OFBiz > purchase order code). > Agreement entities are not currently supported for purchase pricing. See https://fisheye6.atlassian.com/browse/ofbiz/trunk/applications/product/src/org/ofbiz/product/price/PriceServices.java?hb=true#to1323. What's provided in the data model and what Ofbiz code currently does are not always the same thing. lastPrice is used for purchase prices, and it takes no account of quantities. Re your SupplierProduct in Mantle: it has a price and minimumQuantity attributes. Doesn't that mean that if a supplier offers different prices for different quantities, there must be two or more SupplierProducts? Wouldn't it be better to have two different entities, one to record that a product can be obtained from a supplier, and a separate one for prices at various quantities? There would be a one-to-many between the former and the latter. > TaxAuthorityRateProduct > > ------------------------------------- > > > > - This entity is used to determine tax on shipping and promotional > pricing > > adjustments, when these are not products. There should be a separate > entity > > for tax on all order adjustments, which will determine tax based on the > > adjustment type rather than on the product category > > Yes, agreed. In the Mantle UDM I changed the name to > "TaxAuthorityRateSales": > > > https://github.com/jonesde/mantle/blob/master/mantle-udm/entity/AccountingOtherEntities.xml > > > - The ProductStore is used to determine the tax payable. That’s fine for > > sales, but makes no sense for purchases. Really, the ProductStore is a > proxy > > for the geo of the seller. TaxAuthorityRateProduct should be revised to > list > > the geo of the seller rather than the ProductStore, then the same rules > > could be used by purchases as well as sales. For a sale, the ProductStore > > geo would be used as the geo of the seller. > > I'm not sure what you mean about the geo of the seller thingy. The geo used > on TaxAuthorityRateProduct is the taxAuthorityGeoId where the customer is > shipping to (or billing to) a geo where there is a PartyTaxAuthInfo (or > Mantle TaxAuthorityParty) record where the seller organization has > isNexus=Y. > I'm talking about the "buyer" and the "seller" because, for VAT/GST taxes, we need to support tax on purchases as well as sales. For a purchase, we're the buyer and the supplier is the seller. For a sale, the customer is the buyer, we're the seller. TARP *should* be used for both purchases and sales, because what it's really capturing are tax rules we care about, and they don't change just because we're buying something instead of selling it. Governments define taxes in terms of the geographical location of the buyer and the seller, and of course a government doesn't know or care about our product stores. TARP can manage rules based on the geographical location of the buyer, but only has the product store for the seller. That's OK for sales, but breaks down for purchases. I think TARP should have attributes for the geographical location of both the buyer and the seller. In effect, I think the product store is a proxy for the geographical location of the seller. I'm happy to leave the product store in TARP if people think it's useful. While we're talking about TARP, I am unsure whether it should also have flags to say if the tax should be applied to purchases, sales or both, or if the tax type should drive that, so a tax type of SALES_TAX will be taken to apply only to sales, and a tax type of VAT_TAX will apply to both purchases and sales. As for the productStoreId field, it should only be used as an extra > constraint on the "rule" record, and should be fine if null (from the model > perspective, the code may not currently behave that way). > > David E Jones-4 wrote: >> >> I'm writing this to start a thread to discuss: >> >> If you could change ANYTHING in the OFBiz data model, what would it be? >> >> To kick this off here are some ideas I've compiled that have come up over >> the years (many based on feedback from people on this mailing list), or >> that I thought of recently will working on this topic. You can see them >> below... >> >> -David >> >> ======================================================== >> >> - Rename *Role entities to *Party >> - Remove *Attribute and *TypeAttr entities (not generally a good practice >> to use) >> - Remove all status history (*Status) and just using audit on the statusId >> field >> - ProductPrice add quantity breaks, change PK to single field sequenced >> - Get rid of QuantityBreak and use the two simple fields instead in each >> place used >> - Get rid of ProdCatalog*, rework it to ProductStoreCategory, ie directly >> associate to the store >> - Change OrderItemShipGrpInvRes to InventoryReservation, change PK to >> single field sequenced >> - Change Quote to be other types of Order >> - Make Return more like Invoice (ie no adjustment, just use items for >> everything) >> - Instead of OrderItemType, OrderAdjustmentType, InvoiceItemType, >> ReturnItemType just use Shared ItemType everywhere >> - PartyRelationship simplify (single sequenced ID) >> >> - Make prefixes consistent (no suffixes), ie toPartyId instead of >> partyIdTo, etc >> - Change all relationships to PartyRole to be type one-nofk; use plain one >> for Party and RoleType >> - Move most *Type entities to Enumeration values (update seed data, >> referring >> entities, remove *Type) (after this remove all remaining hasTable >> fields) >> - Rework PhysicalInventory and InventoryVariance to simplify, reduce >> dependency on InventoryItem >> - Review use of Appl, Assoc (look at book, make sure consistent) >> - Add PartyIdentification entity, get rid of various fields and other >> entities (maybe even PartyTaxAuthority...) >> - Change GoodIdentification to ProductIdentification >> - Combine PartyContactMechPurpose and PartyContactMech (remove, only use >> entity with purpose, name PartyContactMech) >> - Get rid of NoteData, put noteText very-long field directly on various >> *Note entities >> - Review all entities with large PKs (esp 4+ fields) >> - Review and do something with all createOn/By lastUpdateOn/By fields, >> maybe add an auditing flag for the entity instead of just for a field >> - CustRequest to Request >> - FinAccount to FinancialAccount >> - Invoice handle recurrence? (was using RecurrenceInfo) >> - Get rid of optional ProductFeature concept (conf/etc products much >> better model) >> - Instead of various *Term entities and mapping, just use OrderTerm >> everywhere >> - Cleanup/reorg ShipmentCostEstimate, CarrierShipmentMethod, etc, etc >> - Agreement - make price list easier/cleaner >> - Remove all createdDate, createdByUserLogin, lastModifiedDate, >> lastModifiedByUserLogin fields (use framework defaults, audit-log) >> - Product clean up: move dimenstions to new entity, remove content fields, >> etc >> - Trim down big entities like Product, WorkEffort, etc; for groups of >> similar fields use a more normalized structure >> > > > -- > View this message in context: http://ofbiz.135035.n4.nabble.com/Discussion-Potential-Data-Model-Improvements-tp3390620p3422000.html > Sent from the OFBiz - Dev mailing list archive at Nabble.com.
