At 04:14 PM 25/10/2004 +0100, you wrote:

>Hi All,
>
>I have a stituation here. I am just posting this message because I want to
>understand and share with all other the performance issue regarding tables
>and indexes.
>
>I am developing an application where performance is the most important
>issue.
>
>I have one table called CO = "customer order"
>I have one table called CO_ITEM = "the items of the order CO"
>
>I have one table called INV = "invoice"
>I have one table called INV_ITEM = "the items of the invoice INV"
>
>Now happens that the difference between CO and INV is just some fields.
>Most fields are common for both tables.
>
>Now there is another fact: I need an historic with all changes with CO.
>
>THE SOLUTION I THOUGHT BEST:
>
>1) create a single table INV for both INVs and COs with all the fields.
>2) create a single table INV_ITEM for both INV_ITEMs and CO_ITEMs.
>3) for the historic question I just thought about adding four fields in
>the primary key of both tables (INV and INV_ITEM) .
> The three fields are:
> a) a date field for the historic : dt
> b) a user id : user_id
> c) a field to tell me if the record is a INV or a CO
> d) a field to tell me if the record is the present situation
>
>
>I would like to be sure if this is the best approach or, for instance, I
>should keep more tables for better performance.

I don't think there is a "one-size-fits-all" answer to this question. It's
a mix of personal preference and requirements, really. And requirements for
distribution systems can vary from "brain-dead" to "astronomical".

I prefer to hold just one "items" table to avoid the redundancy. An "item"
has flags on it to indicate status - "ordered", "back-ordered",
"delivered", "cancelled", "invoiced", and so on. The requirements I most
often encounter need to have the order information to hand on an invoice
inquiry, and vice versa. Users select whether they want to view
back-orders, or whatever.

What I do regarding headers depends on requirements, of course. I prefer
to hold the core header data for the whole deal in one table and store the
respective relationships between item/order, item/delivery and item/invoice
in separate intersection tables. This is because requirements often demand
the ability to "split" orders at both supply and invoicing levels --
involving a number of many:many relationships.

There may also be linkages to other parts of the distribution chain
(suppliers, transporters, multiple addresses, multiple billing models, and
so on) that are best done through the intersection tables, rather than
through the header, again to avoid redundancy. So item rows in this model
have a surrogate primary key that subsumes foreign keys to the intersection
tables and the intersection tables link to the parts of the chain that are
relevant to them.

When you are using status flags to select rows, it's usually necessary to
provide the classic workaround to low selectivity problems on indexes over
these flags: defining an index consisting of (Flag, PrimaryKey) and, if
the flags themselves are foreign keys, to adjust query plans to block use
of the FK index and enforce using of the user-defined one.

./heLen