Re: designing for speedy access question

Hi Angus,

Bill Angus wrote:
> 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).
> 
you will need quote, order and invoice in parallel if you would like to 
trace things later.

> 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?
> 
You can do, but there is no need for it. MySQL has limits for file sizes 
given by the operating system. You will hit this limit faster of all is 
in one table.

> 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?
> 
No, you will need as many indexed fields as you have fields to search 
for. Date, customer, document number ...

> 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?
> 
No, it is more or less like the quetion to paint a wall green or red.

But you need to put much more thoughts into the index fields.

Erich

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]