From: Mark Stosberg <mark@summersault.com>
To: pgsql-sql@postgresql.org
Subject: recommended data model for a search?
Date: Tue, 15 Feb 2011 17:04:52 -0500
Message-ID: <ijeta5$prh$1@dough.gmane.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mark@summersault.com
Subject: Re: recommended data model for a search?
In-Reply-To: <ijeta5$prh$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox