Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread Master-Detail & indexes
Tue, Sep 25 2007 5:05 AMPermanent Link

Joze
Hi,

I have a master-detail relation via TWO fields:

MasterFields:='CustomerID; CustomerNAME';

What indexes should I have:

solution A:
#1 by CustomerID
#2 by CustomerNAME
like
CREATE INDEX "CustomerID"         ON "Customers"("CustomerID");
CREATE INDEX "CustomerNAME" ON "Customers"("CustomerNAME");

or

solution B:
#1 by CustomerID+by CustomerNAME
like
CREATE INDEX "MyCustomer" ON "Customers"("CustomerID","CustomerNAME");

Which one would work better?

TIA for advice.

Regards,

Joze
Tue, Sep 25 2007 9:08 AMPermanent Link

Chris Erdal
Joze <Joze.NoSpam.Stihec@nospam.siol.net> wrote in
news:d0jhf3hc58dktun9802roon040u83klp1k@4ax.com:

> Hi,
>
> I have a master-detail relation via TWO fields:
>
> MasterFields:='CustomerID; CustomerNAME';
>
>
> TIA for advice.

Joze,

You may not like this advice, but you asked for it Wink

Assuming your CustomerID is unique in the Master table, just use it on
its own and things'll be faster and simpler.

Also, it is (almost) never, IMHO, a good thing to duplicate names etc in
child tables - somebody always ends up mis-spelling the name e.g. when
it's first inserted, noticing it in one table, correcting it there, and
breaking the link if it was used in a master-detail relationship, or just
getting confused next time they try to find both records together.

I say almost because sometimes you really need to avoid lookups for extra
speed when doing some lengthy processing, but that's probably a hangover
from experience with Access - DBISAM's so fast it can cope.
--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Tue, Sep 25 2007 5:19 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Joze,

<< I have a master-detail relation via TWO fields:

MasterFields:='CustomerID; CustomerNAME';

What indexes should I have: >>

As Chris indicated, you should probably trim down the master table's index
to just the CustomerID.  It should be unique if it is being used as the ID
for customers.  Secondly, just use this index:

#1 by CustomerID

CREATE INDEX "CustomerID"         ON "Customers"("CustomerID");

--
Tim Young
Elevate Software
www.elevatesoft.com

Image