calculated values in a key field

[Douglas J. Steele]

Realistically, you should never store calculated values. You're trying to
store 2 pieces of information in a single field (which is a violation of
database normalization principles), plus (if you keep the other 2 values)
you're storing information redundantly. In addition, having a key based on
name isn't a good idea (What happens if you have also have Paul Mitchinson
as a customer? What happens if Jane Miller gets married and changes her name
to Jane Smith?

If you're still determined to do it, you'd have to put the calculation in
the form's BeforeInsert event so that it calculates the key before inserting
the record into the table. There's nothing you can do at the table level.

 

[Bernard Peek]

Realistically, you should never store calculated values. You're trying to
store 2 pieces of information in a single field (which is a violation of
database normalization principles), plus (if you keep the other 2 values)
you're storing information redundantly. In addition, having a key based on
name isn't a good idea (What happens if you have also have Paul Mitchinson
as a customer? What happens if Jane Miller gets married and changes her name
to Jane Smith?

If you're still determined to do it, you'd have to put the calculation in
the form's BeforeInsert event so that it calculates the key before inserting
the record into the table. There's nothing you can do at the table level.

You can create a query based on the table and base the form on the query
rather than the table. That makes it possible, but it's definitely not
something I would recommend.

 

[BruceM]

Why would you not recommend it? The calculation would be performed on the
fly, which is exactly what you want. The calculated field would be
available as needed, but wouldn't be stored. Or am I missing just what it
is that you do not recommend?

 

[Bernard Peek]

Storing calculated data is always risk. In fact any deviation from a fully
normalised structure has risks. The main risk is that the parent fields and
the calculated filed might be updated separately, or two differnt sets of
parents could produce the same value in the calculated field.

If you want to do this it may be worth doing some more research on how and
why other people have taken different approaches to the problem. The first
question to ask is what benefit do you get from denormalising the table. If
you can get the same advantages without the risks then the more risky method
isn't worth pursuing.

This sort of calculation is sometimes used in big databases where it can
give performance improvements, but a big database probably has someone whose
job is to find and fix the errors that a poor implementation (or a
well-meaning but naive user) can introduce.

The process of using real-word values like a name to generate a shorter key
is called hashing. There are standard techniques to do it, I'm sure Google
will find quite a lot of hits on hashing algorithms. An important part of a
hashing algorithm is a way of handling situations where different input data
generate the same key value.

If I was implementing a hashing system in a database I would set up a
trigger on the database table so that whenever the record was updated the
hash key was re-calculated.

Why would you not recommend it? The calculation would be performed on the
fly, which is exactly what you want. The calculated field would be
available as needed, but wouldn't be stored. Or am I missing just what it
is that you do not recommend?

 

[BruceM]

You missed the point of my question. You seemed to be saying you would not
recommend basing the form on a query. I asked why not, since a calculated
query field is not a stored value. I *never* advocated *storing* the
calculated value, but there is no reason not to calculate a value for
display purposes.

Storing calculated data is always risk. In fact any deviation from a fully
normalised structure has risks. The main risk is that the parent fields
and
the calculated filed might be updated separately, or two differnt sets of
parents could produce the same value in the calculated field.

If you want to do this it may be worth doing some more research on how and
why other people have taken different approaches to the problem. The first
question to ask is what benefit do you get from denormalising the table.
If
you can get the same advantages without the risks then the more risky
method
isn't worth pursuing.

This sort of calculation is sometimes used in big databases where it can
give performance improvements, but a big database probably has someone
whose
job is to find and fix the errors that a poor implementation (or a
well-meaning but naive user) can introduce.

The process of using real-word values like a name to generate a shorter
key
is called hashing. There are standard techniques to do it, I'm sure Google
will find quite a lot of hits on hashing algorithms. An important part of
a
hashing algorithm is a way of handling situations where different input
data
generate the same key value.

If I was implementing a hashing system in a database I would set up a
trigger on the database table so that whenever the record was updated the
hash key was re-calculated.

 

[BruceM]

Thanks for clearing that up. I think I have read about hashing, but not
being involved with any big databases I have decided not to pursue it any
further. As it is there is plenty I don't know about subjects of more
immediate concern.

OK, sorry. I understand. Actually I don't have any problem at all with
basing
forms on queries, it can be a useful technique. My objection was to using
a
hash function to create a key without first doing some research. Using
hashes is a perfectly respectable technique and most database management
systems use it, but it's not easy to do properly.

 

[Burton Roberts]

I would (suggest splitting them into multiple columns under normal
circumstances)

 

[Peter Mitchell]

Hello
Is it possible at all (presumably in a form) to create the value to be
stored in a key field based on another field (or two).
For example, to have the form insert
mitchp for a Customer ID based ona surname of mitchell and a first name
of peter.
I know the calculation - done many many times in a query - and also VB - but
I want the system to actually store the calculated value mitchp.
(Yes most of the real world may use numbers and autonumbers - but
nevertheless ...)

Any suggestions welcome.

Peter