Identifying "filler"

[jim_geissman]

Some columns in transaction tables are "mandatory fields" on the data
entry screens, and as a result tend to accumulate junk entries, where
the user puts something, anything, in the window in order to get the
GUI to accept the screen. This filler isn't as elaborate as Lorem
Ipsum, but more likely characters from adjacent keys on the second row
of the keyboard, like "lkjkljl". This non-data gets in the way of
applications that use the data. I wonder if there is a way to
recognize and ignore this stuff -- I would assume it's a well-known
problem, but I haven't found any literature on it. Any pointers
welcome. And sorry if this is off-topic for a DB group.

Thanks,
Jim Geissman

[Hugo Kornelis]

On 14 Mar 2006 07:21:43 -0800, ji**********@countrywide.com wrote:

Some columns in transaction tables are "mandatory fields" on the data
entry screens, and as a result tend to accumulate junk entries, where
the user puts something, anything, in the window in order to get the
GUI to accept the screen. This filler isn't as elaborate as Lorem
Ipsum, but more likely characters from adjacent keys on the second row
of the keyboard, like "lkjkljl". This non-data gets in the way of
applications that use the data. I wonder if there is a way to
recognize and ignore this stuff -- I would assume it's a well-known
problem, but I haven't found any literature on it. Any pointers
welcome. And sorry if this is off-topic for a DB group.

Hi Jim,

If it is acceptable that people enter rubbish in a field, even so
acceptable that you try to work around the rubbish in your queries, why
even bother to make the field mandatory? Just make it optional - people
won't have to make up rubbish and you can just filter out all NULLs.

All fields, both optional and mandatory, should always have a thorough
checking of the entered data **BEFORE** accepting the input into the
database. Of course, there's only so much you can do. It's much easier
to define a sanity check on an age column than a sanity check on an
address column - after all, "msdfmtry" might sound very unbelievable to
you, but it might be a perfectly valid street name in some foreign
country that your company has to do business with tomorrow.

--
Hugo Kornelis, SQL Server MVP

[Anith Sen]

The only way to get around with existing structure would be to add required
constraints to the table to prevent dirty data from getting into it.

Some designers when faced with similar concerns, often consider splitting
the table to multiple ones to form super-type/sub-type relationships.
Perhaps that might be a better approach to your problem here.

--
Anith