------=_NextPart_000_000D_01C628B4.B2D16710
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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
------=_NextPart_000_000D_01C628B4.B2D16710--

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/win32?unsub=gcdmw-win32 [at] m.gmane.org

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 <mdangus [at] psychtest.com>
>Sent: Feb 3, 2006 2:26 PM
>To: win32 [at] lists.mysql.com
>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/win32?unsub=gcdmw-win32 [at] m.gmane.org

--=_alternative 004D1AE68525710E_=
Content-Type: text/plain; charset="US-ASCII"

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" <johnesimpson [at] earthlink.net> 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 <mdangus [at] psychtest.com>
> >Sent: Feb 3, 2006 2:26 PM
> >To: win32 [at] lists.mysql.com
> >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/win32?unsub=sgreen [at] unimin.com
>

--=_alternative 004D1AE68525710E_=--

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, SGreen [at] unimin.com 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" <johnesimpson [at] earthlink.net> 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 <mdangus [at] psychtest.com>
> > >Sent: Feb 3, 2006 2:26 PM
> > >To: win32 [at] lists.mysql.com
> > >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/win32?unsub=gcdmw-win32 [at] m.gmane.org

------=_NextPart_000_0017_01C62BBA.2AD85590
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Actually both styles work, as long as one copies and keeps an "old =
record" and makes a new one when a new activity makes this necessary. I =
took the advice to keep the records all in one table. I made a doubled =
linked list by including a parent-id and child-id field in the row (zero =
if none applies). Then I made a new record (marking the old with =
non-zero child) if there was significant activity. Daisy chaining the =
records in this way just made it easier for me to have a delete function =
that would not leave orphans in the file. But Shawn is right, it also =
allows coherent history. I didn't think of all of Shawn's points. I just =
knew I had to keep all the history for invoicing/return and shipment of =
partial shipments/backordered products. I allowed =
overwriting/modification of quotes or invoices at the operator's =
discretion.

John's three-columns approach (check-box style fields) seemed to me to =
have much the same function as a one-column enum field (or a numeric =
type-code field) , so that seems to be just a matter of choice.

Thanks for all the help!

Bill Angus, MA
http://www.psychtest.com
----- Original Message -----
From: John E. Simpson
To: SGreen [at] unimin.com
Cc: Bill Angus ; win32 [at] lists.mysql.com
Sent: Tuesday, February 07, 2006 7:00 AM
Subject: 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, SGreen [at] unimin.com 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" <johnesimpson [at] earthlink.net> 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 <mdangus [at] psychtest.com>
> > >Sent: Feb 3, 2006 2:26 PM
> > >To: win32 [at] lists.mysql.com
> > >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
> >


------=_NextPart_000_0017_01C62BBA.2AD85590--

The two models proposed are captured in the Enterprise Architecture Pattern=
s:
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 <johnesimpson [at] earthlink.net> 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, SGreen [at] unimin.com 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 T=
LC
> >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" <johnesimpson [at] earthlink.net> 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 <mdangus [at] psychtest.com>
> > > >Sent: Feb 3, 2006 2:26 PM
> > > >To: win32 [at] lists.mysql.com
> > > >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/win32?unsub=3Dpaul [at] the-lyons.co=
m
>
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32 [at] m.gmane.org

> > > > -----Original Message-----
> > > > >From: Bill Angus <mdangus [at] psychtest.com>
> > > > >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)
- jonathan.lampe [at] standardnetworks.com

******************* 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/win32?unsub=gcdmw-win32 [at] m.gmane.org