We have an application that manages RSS feeds for searches. Our first
attempt at modeling this was to create a table for the searches with the
standard integer primary key generated from a sequence, and then have
one column per search key. Each row should represent a unique search.
When a new search comes we need to look up the key/value pairs received
and find the unique search ID that corresponds to it. ( IE, we need to
know whether we have an "insert" case or an "update" case, without the
benefit of being provided the primary key for the update case. )
The problem with this design is that there are 13 potential search
terms, and the "unique" index we would like to create to avoid
duplicates and speed-up searches would necessarily span all 13 columns.
And further complicating the matter is the handling of nulls, which need
to be considered as part of the unique signature of a search, but are
not easily indexed that way.
Having gotten this far, I'm taking a step back to consider if there's a
better way to design this. Perhaps there's a standard solution for this,
but I'm not sure where to find it.
Here's one idea I've had for a refined design: Each search can be
represented as a unique, sorted query string. It would be easy for the
application to compute this and then make an MD5-hash of it (or
similar). The MD5-hash would then be used as the unique key instead of a
standard integer. This would then be easily indexable for quick
look-ups, and would allow us to create a unique index that doesn't have
a problem with null values, too.
Is this a reasonable approach, or is there a better design I'm overlooking?
Thanks,
Mark
