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
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 |