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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql