Re: designing for speedy access question

Actually both styles work, as long as one copies and keeps an "old record" and makes a new one when a new activity makes this necessary. I took the advice to keep the records all in one table. I made a doubled linked list by including a parent-id and child-id field in the row (zero if none applies). Then I made a new record (marking the old with non-zero child) if there was significant activity. Daisy chaining the records in this way just made it easier for me to have a delete function that would not leave orphans in the file. But Shawn is right, it also allows coherent history. I didn't think of all of Shawn's points. I just knew I had to keep all the history for  invoicing/return and shipment of partial shipments/backordered products. I allowed overwriting/modification of quotes or invoices at the operator's discretion. 

John's three-columns approach (check-box style fields) seemed to me to have much the same function as a one-column enum field (or a numeric type-code field) , so that seems to be just a matter of choice.

Thanks for all the help!

Bill Angus, MA
http://www.psychtest.com
  ----- Original Message ----- 
  From: John E. Simpson 
  To: [email protected] 
  Cc: Bill Angus ; [email protected] 
  Sent: Tuesday, February 07, 2006 7:00 AM
  Subject: Re: designing for speedy access question


  All good points. As you say, there are a lot of variations possible, 
  depending on business model. The structure I outlined covers only a 
  pretty simplistic scenario.

  At 09:07 AM 2/7/2006, [email protected] wrote:
  >The drawback to the single-record design is that you lose some degree of
  >history.  What if you submitted a quote and they only ordered 6 of the 8
  >items at the prices you quoted? What if you could only invoice them for 5
  >of the 6 pieces because of supply shortages?  Without each of the three
  >records independently available (either all in one table or in three
  >separate tables), you lose the ability to compare the progress of this
  >sale  at each stage of its development. Some clients will require more TLC
  >and may need 5 or 6 quotes. They may cherry pick one or more quotes to
  >build a finished order.
  >
  >All of this may be mitigated by your current business practices and
  >business model but I think it would be better to leave your options open
  >and keep all of your records rather than to overwrite just one whenever it
  >changes status.
  >
  >
  >Shawn Green
  >Database Administrator
  >Unimin Corporation - Spruce Pine
  >
  >
  >
  >"John E. Simpson" <[email protected]> wrote on 02/03/2006
  >03:14:26 PM:
  >
  > > Just my opinion, but I think what I'd do would be create one table,
  > > called Orders say, and have it include three columns (in addition to
  > > whatever else you'd want): quote_no, order_no, and invoice_no (exact
  > > names don't matter of course). Create indices on all three. Then
  > > forget about a separate RecordType field.
  > >
  > > -----Original Message-----
  > > >From: Bill Angus <[email protected]>
  > > >Sent: Feb 3, 2006 2:26 PM
  > > >To: [email protected]
  > > >Subject: designing for speedy access question
  > > >
  > > >I am trying to design an app that will store quotes, customer
  > > orders and invoices (where invoices are completed customer orders
  > > and have the same format, and quotes are optional, but similar to
  > > orders -- so that a quote can simply become an order when the
  > > customer accepts it).
  > > >
  > > >Is it reasonable to store invoices quotes and orders in the same
  > > table, only classifying which type a record is by using an integer
  > > RecordType field?
  > > >
  > > >If so, how is the best way to set up the index files so that for
  > > example, speedy access of invoices by customer takes place and mySql
  > > server doesn't work hard to filter quotes and customer_orders in
  > > order to locate invoices?
  > > >
  > > >Is it sufficient to index the file on RecordType? Will MySql
  > > automatically use such an index to speed up queries which might
  > > select invoices by customer (or by some other field) but not
  > > unfilled orders or quotes? Or do I have to specifically need to set
  > > up and index on both RecordType and CustomerNumber to design for
  > > speediest possible access?
  > > >
  > > >Finally, is my general design idea flawed?... i.e. is it actually
  > > better to make 3 separate files having more or less the same schema,
  > > and not to store the data in a single file?
  > > >
  > > >Thanks and have a great day!
  > > >
  > > >Bill Angus, MA
  > > >http://www.psychtest.com
  > >