agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Advice - indexing on varchar fields where only last x characters known
Date: Thu, 17 Oct 2013 12:16:30 -0700 (PDT)
Message-ID: <1382037390671-5774944.post@n5.nabble.com> (raw)
In-Reply-To: <201310171220.44550.gary.stainburn@ringways.co.uk>
References: <201310171220.44550.gary.stainburn@ringways.co.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Gary Stainburn wrote
> However, it means that every time I'm trying to connect various tables up
> using foreign keys
The degree to which each input source guarantees uniqueness of a given VIN
matters. Keep in mind that any system that required the user to manually
enter the VIN has the propensity for errors. Either outright invalid VINs
or marginally correct VINs with typos (which mean the VIN might be less or
more than 17 characters even if the 17-character version was intended).
Specifically it is not uncommon for the VIN to be made-up when it is a
required field but the user does not know what the VIN is.
6 characters are unique within a model year but you need at least 8
characters to be generally unique for a given manufacturer.
For foreign key purposes it may be worthwhile to generate a "matching" table
and then during import use an algorithm to match up different records. Then
during general queries that table can be used for joins. In this way you
only pay the price of matching once and that during import as opposed to
during user requests. Having a canonical VIN table helps here though during
import that table then has to be maintained. The added advantage is that
such a mapping table allows you to search against a single table and such a
table (and likely its indexes) should be fairly small so as to make good use
of memory.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-indexing-on-varchar-fields-where-only-last-x-characte...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (11+ messages) latest in thread
Message-ID: <1382037390671-5774944.post@n5.nabble.com>
Permalink: ../1382037390671-5774944.post@n5.nabble.com/
Also on: postgresql.org/message-id/1382037390671-5774944.post@n5.nabble.com
reply
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: polobo@yahoo.com
Subject: Re: Advice - indexing on varchar fields where only last x characters known
In-Reply-To: <1382037390671-5774944.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox