Re: designing for speedy access question

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
> 
> 
> ===================================== 
> My computer beat me at checkers, but
> I sure beat it at kickboxing. (Emo Philips)
> 
> 
> -- 
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:    http://lists.mysql.com/[email protected]
>