10 Replies Latest reply on Feb 9, 2015 6:09 AM by electon

    Data Integrity

    electon

      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.

        • 1. Re: Data Integrity
          Markus Schneider

          WE got items with 'no stock' flags, means there is no stock calculation behind a product (typical for hourly rates, etc, we keep goods and work in one tabel). Further on, one can add an item to the invoice that is not in the products table. Those items are 'flagged' (red colored text), so an admin can 'adjust'  the missing things afterwards. Product category can be set automatically to 'diverse', whatever.

          THis works quite well - as long as there is no need to track vendors and one has only one stock.

           

          WOuld be method 3 - with quite a lot of work for the administator and functions to alter sold items (vendor, etc)... every other method may take too much time while a customer is waiting - but this depends...

          • 2. Re: Data Integrity
            gdurniak

            PDF's can often be converted to text,  otherwise hire a kid to type it in

             

            greg

             

            > but their price list is only in PDF or a printed book and not importable

            • 3. Re: Data Integrity
              electon

              @greg

              LOL. I have done the PDF to text before, it’s a pain if it’s a catalog with pictures. No easy way to convert them into tables.

              Wouldn’t let Bart Simpson do it, he’ll type the same thing 100 times over.

              I get your point, I guess pointing to No1: get the products right first!.

               

              @rem-software.ch

              Adding to an invoice is not a problem, all the data describing the item is stored there anyway, same for customer info in case a product or client info changes or is deleted.

              The issue is the item’s “life" begins as the customer is asking for an item, may also buy something that he actually can take away :-). In this case the item not in stock has to be backordered.

               

               

              The thing is: if not in the product table, all this extra description hast to be brought over everywhere the item goes ( ordering, receiving, shipping, invoicing again and again ). This forces the same behavior for all database products.

               

              Thanks for the feedback, keep it coming!

              • 4. Re: Data Integrity
                wimdecorte

                electon wrote:

                 

                 

                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 ).

                 

                While I lean towards #1 (the data exists, it is just not in the database so fix that first), #2 is not as bad as you make it sound.

                 

                Counter staff can pick a generic "loose item" from the product table so that you have a trackable product ID.  Then they type in a description.  They would not be messing with critical data, you shield that away.

                 

                Moving the data around to other parts of the system is not more difficult then than doing that for another product.

                • 5. Re: Data Integrity
                  siplus

                  I would not dirt an existing, full integrity system with volatile data. I would build a separate system dealing with these exceptions. Separate invoices, too. Once a product gains stability, it goes from the volatile database to the stable one. Until then, I would keep 2 separate systems.

                   

                  I don't want exceptions. I don't want tons of if's and parametrised scripts to run around exceptions. Exceptions belong to a separate world where they are not second-class citizens and are allowed to be what they are, but when they ring the main door bell the whole system knows that it's dealing with .. exceptions. And the secular, nailed-to-the-standard data will still run on its own, well-established, straight path.

                  • 6. Re: Data Integrity
                    taylorsharpe

                    I would particularly agree that item 2 is not as bad as you think.  It would at first, but over time, there will be less and less exceptions and need to input a product at the counter.  Of course the other challenge is that the same product is put in several different ways. 

                     

                    However, if you're committed to running a business right and not having messed up records, #1 is the proper way to go.  If you can't control inventory and products, you're probably also getting cheated by customers and/or employees.  Business Process that have poor transactional tracking are easy to circumvent invite people to cheat you and not the way to go in the long run. 

                    • 7. Re: Data Integrity
                      BarbaraCooney

                      I am in the same position. However, items will never become products and the only "inventory" there is are items received that have not yet shipped. I simply track the order_item id through the process.

                      • 8. Re: Data Integrity
                        mtwalker

                        In our invoicing solution we do 1 and 2. There is a main product catalog that the user can pull from or they can add a line item and enter the product/service description. In our case every product must be assigned to a category so when an item is manually added the user is asked to assign it a category. They are also given the option to add it to the product catalog.

                        • 9. Re: Data Integrity
                          keywords

                          Re: "The thing is: if not in the product table, all this extra description hast to be brought over everywhere the item goes ( ordering, receiving, shipping, invoicing again and again ). This forces the same behavior for all database products."

                           

                          Have you considered creating a separate Requests table. It could work as follows:

                           

                          1.     The ordering interface has a portal where the line items are listed, and a second portal for Requests

                          2.     If the customer wants a Thingo but it is not in the Product list, then he/she makes an entry in the Request portal

                          3.     An entry here creates a Requests record with whatever detail your system requires the customer to enter

                          4.     Once the order is placed by the customer, an administrator can address the Request record and obtain stock, add the item to the Products table so it is listed for future orders, communicate with the customer that it can't be obtained, whatever …

                          • 10. Re: Data Integrity
                            electon

                            Once again Thank You all for the input!!! If there only was a way to mark all as correct answers...

                            I'll try to recap and give my own feedback.

                            wimdecorte

                            Counter staff can pick a generic "loose item" from the product table so that you have a trackable product ID.  Then they type in a description.  They would not be messing with critical data, you shield that away.

                             

                            Moving the data around to other parts of the system is not more difficult then than doing that for another product.

                            It gets problematic, db relationally if there are 3 items from products and more than one not. In this case they would relate to same id.

                            I can move data around and indeed it makes no difference in that sense.

                            Maybe I didn't make my question clear enough:

                            The problem is on OOH with displaying data and OTOH database normalization / integrity.

                            For critical data ( invoice, sales order ) all essential product description is in their line items tables. All other places mostly product id is used. Let's say there's a typo or some other error or configuration changes for the product, once corrected in products table it will reflect in relevant tables, ie. PO that's not been sent yet, etc.

                            What I mean is to get the item info through a relationship to products and since I can put only one related field on the same spot to display data...

                             

                            BarbaraCooney

                            I am in the same position. However, items will never become products and the only "inventory" there is are items received that have not yet shipped. I simply track the order_item id through the process.

                            Looks like purchase orders are not automated then, I'm aiming for automatic PO creation / sending. You have your one id to rule them all already.

                             

                            keywords

                            Have you considered creating a separate Requests table. It could work as follows:

                             

                            1.     The ordering interface has a portal where the line items are listed, and a second portal for Requests

                            2.     If the customer wants a Thingo but it is not in the Product list, then he/she makes an entry in the Request portal

                            3.     An entry here creates a Requests record with whatever detail your system requires the customer to enter

                            4.     Once the order is placed by the customer, an administrator can address the Request record and obtain stock, add the item to the Products table so it is listed for future orders, communicate with the customer that it can't be obtained, whatever …

                            I have but I wouldn't want to create a separate table / portal for various reasons. One is I'd like to keep all line items in one place.

                            Second is: I mean to get the item info through a relationship to products and since I can put only one related field on the same spot to display data... ( there are complicated workarounds always).

                            The thing with adding it later to products table gives me the shakes since working it backwards from there onto those items is prone to errors ( users upon entering data may have already put same item in twice, one with a typo... ) it could trigger some lookups when undesired.

                             

                            Anyway.

                            I think I'll go this way:

                            No1: Strongest preference to getting the products right first, then

                            No2: Add an item to products on the go while checking for existing same items, missing vendors that need to be created, marking that record as "for revision", etc. and take my chances.

                            This way the product database can gradually end up in the most desired No1 stage with some care.

                            A check is performed anyway before the item can go out with a purchase order and not allowed if something is wrong.

                             

                            Feel free to comment.