recommended data model for a search?

From: Mark Stosberg <mark(at)summersault(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: recommended data model for a search?
Date: 2011-02-15 22:04:52
Message-ID: ijeta5$prh$1@dough.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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

Browse pgsql-sql by date

  From Date Subject
Next Message Jasen Betts 2011-02-15 23:32:50 Re: Determine length of numeric field
Previous Message Tony Capobianco 2011-02-15 21:14:35 Re: Determine length of numeric field