Re: designing for speedy access question

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]