Hi all, maybe the topic isn't entirely appropriate...
In a common invoicing database scenario like the Invoicing Template from Filemaker there is a Products and Invoice Line Items table.
This is the best for data manipulation: product id goes into line items and then lookups and calculations can be performed based on the existing product.
That's how "sensible people" do it. First get the product right, then can go on from there.
How about a situation when the item does not exist in the database, one example is there are vendors that may have up to 500 items but their price list is only in PDF or a printed book and not importable, nothing is in stock, order as you go, never know what the customer at the counter shall order.
The item must somehow enter the system as a "loose item" and then the fun begins...
For purpose of sales orders, PO, receive, inventory etc... it's very easy to track and move the product id about in the system but this case proves very challenging. The item must have a vendor id, supplier id, sku, description, price and cost. In this case all that data would have to be kept everywhere it goes and separate logic for both situations ( product / non product ).
I would love to hear your opinions on this matter. My own ideas are:
1: Force the administrator to get their product database right, no matter the time ( decide what to sell upfront and stick to that ).
2: Add the product to the database, at the counter while the client is waiting ( I see it as asking for database pollution because the staff would have to do it ( could be anyone messing with critical data ).
3: Design a system that can track items not based on product id.