236263d7   
   From: isgdre@HotMail.com   
      
   --CELKO-- wrote:   
   >>> Hi, I have a table that contains order information, each row contains   
   information about a detail item that's been order but the key is order_id and   
   line_nbr.  <<   
   >   
   > Line number, as it the PHYSICAL location from a PHYSICAL piece of   
   > paper or input screen? This is a common newbie design error that leads   
   > to extra code and lots of screw ups.   
   Not sure how that would help.  Microsoft built the program.  I have no   
   source code for it.  All I can do is live with the problems.   
      
   On the other hand.  if I was to do it I may have done the same thing.   
   With multiply destinations, and time frames with partial shipments and   
   split carriers etc.. etc.. etc.. a properly normalized database model   
   would be very big and cumbersome.   
      
   Also, I would have to create a cross conversion (sort of) to suck in all   
   the info in and show it to the human in a understandable grid format   
   because that's how his wet-ware deals with it anyway.   
      
   Besides, I'm going to pick my battles on this one.  There are a lot more   
   serirose arbitrary boundaries and goofy data then the likes of a order   
   form and if I had the power to make Microsoft make schema changes to   
   something that's a little more 21st century I'm sure the order form   
   wouldn't make the top 100 of them to change.  :)   
   >   
   > The customer is actually ordering the item and does not care about a   
   > line number.  This is confusing the car with the parking space.   
   >   
   > You are supposed to be build a LOGICAL data model, so you should have   
   > a key made up of (order_id, item_nbr) where item number is your SKU,   
   > UPC, EAN or other industry standard product identifier.  Nothing to do   
   > wiht the paper forms!   
   >   
   >>> I'm trying to insert information into the table from another table but   
   other table does not have any type of line number information.  Also there is   
   no hard value for the number of rows already in the order detail table.  <<   
   >   
   > Why would the PHYSICAL parking space numbering in one lot have   
   > anything to do with a totally different building? Think about it!   
   > Next you will be using physical row numbering in SQL to mimic a 1950's   
   > paper file system or magnetic tape.  A properly designed schema might   
   > have something like this:   
   >   
   > INSERT INTO OrderDetails (order_id, upc, order_description)   
   > SELECT order_id, upc, '!NOTE!' -- pray for defaults on other columns   
   >  FROM OrderNotes;   
   >   
   > But a better question is why not just do an UPDATE instead of an   
   > INSERT?  This row is missing quantity, unit price, etc that you would   
   > expect in an order detail.   
   No, we are adding shipping notes and other instructions to the bottom of   
     an order so the print outs will all have them.  ( Yes, Paper. Like all   
   paper-less offices.  We generate even more paper ).  I can create new   
   reports and join the info to the headers and just show them at the end.   
     That's been a kind of good solutions but some of this is using stock   
   reports and other applications.  Also, edi,xml interfaces are based on   
   the order lines so it's easier just to add them there and not change   
   everything else (witch again we don't have source to).   
      
   >   
   > UPDATE OrderDetails   
   >    SET order_description = '!NOTE!'   
   >  WHERE EXISTS   
   >        (SELECT *   
   >           FROM OrderNotes AS N   
   >          WHERE N.order_id = OrderDetails.order_id   
   >            AND N.upc = OrderDetails.upc);   
   >   
   > You data element names are also vague which is also a sign of bad   
   > schema design.  You need to re-think this whole thing after you read a   
   > book on data modeling and RDBMS.   
   >   
      
   Thanks for the thoughts.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)