Question on Data Storage, Relationships & Performance

[Ian Baker]

We have a Acess 2k, 2k2 & 2k3 db split BE on the server & FE on each PC. It
is currently developed in the "normal" way <grin> of using an ID number
field as the record ID with the ID value being created by Me!ContactID =
Nz(DMax("ContactID", "tblContact")) + 1 in BeforeUpdate of a NewRecord eg
EmployeeID or ContactID
EmployeeName ContactName

And the record ID value is stored in relevant related tables i.e. for sales
orders
SalesOrderID
ContactID
EmployeeID

To cut a long story short our clients would prefer a form combo box on new
records to not include eg terminated employees but to still show the
terminated employee's name in the field of records that were created when
they were employed. This would require storing the text value of
EmployeeName instead of the number value EmployeeID in the table plus having
a "include" type of field like "Terminated" in the Employee table etc.

My concerns are:
1. This would cause a much larger BE having to store so much more data
2. Having to pull more data (number vs text) across a network will slow down
performance
3. Having relationships between text values instead of smaller number values

Please, can anyone comment on these concerns and is there any other issues
with doing this that I should be concerned about (doing this would require a
major restructure of the entire foundations of the app - ouch)

 

[Joe Fallon]

Some comments:

1. This would cause a much larger BE having to store so much more data.
Negligble. NOt a concern.

2. Having to pull more data (number vs text) across a network will slow down
performance.
Zero problem here too.

3. Having relationships between text values instead of smaller number values
Not an issue. Access is very fast and you will never notice the difference.

Also, I am not sure what the real problem is.
Why not have a Status field on the Employee record?
If the Status = "D" (De-activated) then
exclude the record from the cbo query.
But include the record when retrieving historical data.

 

[Ian Baker]

Thanks Joe
I forgot to add that I am using Jet & DAO

I have spent just about the same amount of time planning/designing the db as
development and I am very cautious about performance especially over a
network.

Thanks for you replies to my concerns so it seems I am worrying over nothing
except for the huge amount of work I have in front of me now.

Yes, excluding the record from the cbo by having a "exclude" field would be
another approach. It would have to be triggered, possibly by the OnEnter of
every cbo, as it couldn't exclude the record when browsing through the forms
records as it would need to be included to display the value being tied to
the recordID field.

So, my choice is to either save the text value in the table which would have
no real concerns, or to use the "exclude" only when entering or selecting
from the cbo which, off the top of my head, would mean OnEnter code to
change the RowSource to a different query (also meaning double the number of
queries) on every cbo. Hmmm that sounds like a hard choice, any thoughts?

 

[John Vinson]

Yes, excluding the record from the cbo by having a "exclude" field would be
another approach. It would have to be triggered, possibly by the OnEnter of
every cbo, as it couldn't exclude the record when browsing through the forms
records as it would need to be included to display the value being tied to
the recordID field.

There's a trick that's often needed when you have a conditional combo
box in a continuous form, but it's applicable here too. Base the Form
on a query joining the table to the employee table so you have the
employee name available. Carefully superimpose a Textbox in directly
over the text portion of the EmployeeID combo box; bind it to the
employee name field from the linked table; set its properties Locked =
True, Enabled = False, Tab Stop = False so the user can't do anything
with it other than see it.

This will let the combo appear to display all employees, regardless of
status; but you can base the combo on a query selecting only the
employed ones. When it's dropped down it will come in front of the
textbox and those are the only ones you'll see.

 

[Ian Baker]

Thanks guys
It seems no matter which way I go there will be some small performance
concern allthough being negligable. All cbo's having one control over
another and having forms based on queries (small impact), resetting the
RowSource on all cbo's by an OnEnter event and changing it back OnExit plus
extra queries (small impact) or saving the text value in the table
increasing the BE size and network traffic (small impact).

The app is a help desk app and currently withstands 25 concurrent users with
no problems, have never experienced any corruption or performance problems
which can only mean the effort one takes when designing is well worth it.

I think it comes down to the amount of work I want to do and the benefits of
each solution. By saving the text value I can allow meaningful data to be
displayed in Excel to do further analysis in pivot tables etc instead of
recordID values eg instead of Employee diplayed as 8 it would be "John
Smith". Using both other ideas would mean FE changes only but extra code and
extra events adds another element of potential risk (you never know what a
user is going to do).

Anyway thanks again guys for the ideas - very much appreciated!