Re: Advice - indexing on varchar fields where only last x characters known

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-characters-known-tp5774839p5774944.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.