...

View Full Version : Treat certain characters in database fields as nonexistant in searches



]|V|[agnus
07-28-2004, 08:21 PM
This client wants hyphens, underscores and both slashes to be considered as if they aren't there when users do searches on their inventory, but they still want those characters to be displayed as the actual product name when the results come up.

So, the easy solution of changing the code that inputs the data into the database to strip out those characters ahead of time is out of the question. This leaves me back at a bit of a block, since I can't really think of a way to achieve this desired result then. One of the only seemingly feasible solutions I've got in my head is to grab ALL records and throw them into an array, stripping out the characters along the way, but that would just be a ridiculous amount of looping, querying and excessive use of resources, I think.

The language I'm working with is ColdFusion, but specific language syntax aside, I'm still trying to figure out how this might be done conceptually. I'm not sure it can be, but I thought I'd see what y'all thought. Thoughts? ;)

raf
07-29-2004, 12:26 PM
just create an extra column where you store the value after removing the hyphens etc.

So you'll have the original value and the stripped one. You then run your search against the stripped one, but only select the original column.

but of redundant data but it wount have any impact on performance. Will just take up a bit more space.

]|V|[agnus
07-29-2004, 03:05 PM
Sure, and that is in fact what I ended up doing... I was just being the idealist that I am and trying to figure how it might be done without the redundancy. :thumbsup:

raf
07-29-2004, 03:14 PM
|V|[agnus']Sure, and that is in fact what I ended up doing... I was just being the idealist that I am and trying to figure how it might be done without the redundancy. :thumbsup:
A true idealist should always put runtimeresources and processingtime first :D (certainly if the downsides in terms of extra required space and needing to always insert/update 2 columns instead of 1 are so minor)

I think that when it comes to searches and filtering out records, that your db should be optimised for that because retrieving large resultsets and then processing them in your applicationlayer always consumes much more resources then if you can do it with one simple query.

]|V|[agnus
07-29-2004, 03:19 PM
Hmm, perhaps, but I kinda feel like this sort of solution is, in a conceptual way, akin to using tables for layout. It's a sloppy workaround of sorts in the absence of the proper tools to do it "right".

Now, that doesn't mean it shouldn't be done this way. Obviously, I've come up with nothing better, but you see my point I hope.

raf
07-29-2004, 03:58 PM
|V|[agnus']Hmm, perhaps, but I kinda feel like this sort of solution is, in a conceptual way, akin to using tables for layout. It's a sloppy workaround of sorts in the absence of the proper tools to do it "right".

Now, that doesn't mean it shouldn't be done this way. Obviously, I've come up with nothing better, but you see my point I hope.
No i don't.

As with many db-processes, you need to make a decission between having a completely normalised db without any update anomalys and a performant system.
Since your db powers a website where responsetimes need to be kept as low as possible, there is no reason why you shouldn't choose for the most performant sollution. Especially not since the downsides are so minor. (You will however no longer be able to where your "My db is fully normalised" T-shirt)
There are probably ways to do some sort of regex-search or replace on the values you then match against, but this will all consume more resources then simply having a cleaned value in another column.

Your comparison to using tables for layoutpurposes is not valid. Tables generally require more server side code to create them, produce more sourcecode, usualy require spacergifs etc. As a general rule, using tables for layout will consume more runtime and bandwidth. So even from a purely performance and resources standpoint, tables should not be used for layoutpurposes and in quite a few cases, even not for tabular data.

(luckely for us, machines have become so powerfull and RAM and bandwidth has become so cheap, that we don't realy notice most resource spilling code and designs)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum