Re: designing for speedy access question

The two models proposed are captured in the Enterprise Architecture Patterns:
Class Table Inheritance - A table for each entity type
Single Table Inheritance - A single table, holding multiple types
distinguished (usually) by a Type field.

If you have a copy of Martin Fowlers book, Patterns of Enterprise
Application Architecture, you could consult the discussion of the
relative pros and cons in there. But I'm sure Googling for those
pattern names will turn up some useful results too....

Regards..Paul

On 2/7/06, John E. Simpson <[email protected]> wrote:
> 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]
>
>
>

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