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


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