To: pgsql-sql@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: recommended data model for a search?
Date: Tue, 15 Feb 2011 17:04:52 -0500
Lines: 35
Message-ID: <ijeta5$prh$1@dough.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7


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