Indexes or double references?

[markmarko]

My database will have tblSalesOrders and tblInstallationOrders. Often, a
SalesOrder will be associated with an InstallOrder (when our company sold the
service and installed it). Both of those tables will likely have 10's of
thousand, and eventually hundred of thousands of records.

After a SalesOrder is entered, user can click a button to create the
associated InstallOrder, which places the SalesOrderID into the Install order
record. So, if user is on a particular InstallOrder, finding the associated
Sales Order is easy enough, but what about the other way? Once the tables get
really large, if the user is on a SalesOrder (which does not have a field
referencing the InstallOrder) is there a problem with it searching through
all the records to find a matching InstallOrder. Will it really slow down?

My actual question is this... Would it be better to include a field in the
SalesOrder that can store the associated InstallOrderID, or to index the
field I already have in the InstallOrder which identifies the associated
SalesOrderID?

 

[John W. Vinson]

Would it be better to include a field in the
SalesOrder that can store the associated InstallOrderID, or to index the
field I already have in the InstallOrder which identifies the associated
SalesOrderID?

One or the other. The choice depends on the numbers: which of these statements
is true (or most likely to be true)?

Each SalesOrder will have zero, one, or several InstallOrders; each
InstallOrder pertains to one and only one SalesOrder.

Each InstallOrder may pertain to one SalesOrder, more than one SalesOrder, or
may not be associated with any SalesOrder at all; each SalesOrder will have
either zero or one InstallOrders, never more.

One SalesOrder may generate multiple InstallOrders; several different
SalesOrders could pertain to the same InstallOrder.

John W. Vinson [MVP]

 

[markmarko]

The first choice is closest...

Each SalesOrder will have zero, one, or (very rarely) several InstallOrders.
Each InstallOrder will have zero or one SalesOrder (never more than one).

 

[John W. Vinson]

The first choice is closest...

Each SalesOrder will have zero, one, or (very rarely) several InstallOrders.
Each InstallOrder will have zero or one SalesOrder (never more than one).

In that case, put a SalesOrder_ID in the InstallOrders table, and define a
relationship. You could put the install orders subform on the Sales Order form
using the SalesOrder_ID as the master/child link field if you want it to fill
in automatically.

John W. Vinson [MVP]

 

[Tony Toews [MVP]]

My actual question is this... Would it be better to include a field in the
SalesOrder that can store the associated InstallOrderID, or to index the
field I already have in the InstallOrder which identifies the associated
SalesOrderID?

You've already got an answer. However I would do just about anything
to avoid fields in two tables pointing to each other. This will be
troublesome when it comes to ensuring it is always accurate, etc, etc.
So just index the field you already have in the InstallOrder table.
Which will happen automatically when you create the join anyhow.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[markmarko]

Thanks Tony, that was the kind of advice I was looking for.