Re: designing for speedy access question

> > > > -----Original Message-----
> > > > >From: Bill Angus <[email protected]>
> > > > >Sent: Feb 3, 2006 2:26 PM
> > > > >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?

I'd store these all in the same database.  I've been involved in a similar 
applications that store drafts and finals in the same database.

I would, as others have suggested, store COPIES rather than twiddling flags 
on the originals so you can go back and see what changed.  (Perhaps store a 
"version number" with each new copy.)

> > > > >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?

Index the "type" flag, to be sure, and practice with the "USE INDEX X" and 
"EXPLAIN" commands.

You may also want to create a "current" database with a JOIN to the most 
current version of the quote/invoice and an overall "status" flag (sent to 
customer, paid, etc.)

> > > > >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?

Generally, if you ever find yourself saying something like...
"3 separate files having more or less the same schema"
...you really should be thinking "one database table with a type field 
bearing 3 different values"
(Schema = table)


- Jonathan Lampe, GCIA, GSNA
- MOVEit Product Manager
- Standard Networks, Inc.
- 608.227.6100 (US - CST)
- [email protected] 

******************* PLEASE NOTE ******************* 

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. 


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