Re: Re: Advice - indexing on varchar fields where only last x characters known - Mailing list pgsql-sql

Craig,

We only deal with Ford and Kia new cars. Thankfully, I am only dealing with 
pre-sales / sales at the moment. 

After-sales is already catered for. VIN validation there is already dealt with 
as once the VIN is entered it is used to access manufacturer's warranty / 
aftersales databases.

On Friday 18 October 2013 14:44:12 David Johnston wrote:
> A better UI would be to list all matches and let the user pick.  Zero
> matches could also result in a similarity search...

I have built this facility into the UI using onKeyUp events to trigger AJAX 
(onchange only works once the field loses focus). Although this greatly 
increases accuracy on user input it does increase the load on the SQL server 
as entering a single VIN generates a number of searches.

>
> The nature of VINs make them poor FKs.  This is one case where I either use
> a surrogate key or, in most cases, some other natural key and leae the VIN
> as a descriptive attribute.  The VIN is often of secondary priority. Stock
> numbers and invoice numbers are the primaries.

Where possible I do use SERIAL for primary keys and foreign keys. However, 
this method still requires the initial search to be done using real data to 
make the initial pairing.  There are a number of stages that have to to be 
actioned between vehicle order and customer handover. Unfortunately, many of 
these actions occur asynchronously so I cannot guarantee certain pairs will 
exist before others are required.

You make a valid point about stock numbers making a far better PK and where I 
have one I use it. 

However, as an example. a factory order from Ford won't even have a VIN 
allocated until it reached sheduled (build start date allocated), so the only 
reference I have is the Ford Order Number. The FON is the link from our 
internal order to the vehicle. Then once the vehicle is scheduled I get a VIN 
too. When the vehicle is received into our compound it is booked in by 
scanning the VIN. This then links to the FON, and back to the order. The 
stock number only gets allocated after the vehicle has been booked into 
stock. Things then become much easier, which is good because that's also when 
it becomes busier.

>
> I haven't done much work with cross-department linking of vehicles; mostly
> worked in a vertical fashion so a vehicle is informative.  I also only have
> USA exposure.
>
> As to the trigger usage: it is not redundant information.  One column
> contains raw data and the other is normalized.  While they both represent
> the same concept generally the specific characteristics makes them
> non-redundant in the model.  A surrogate key is, in many ways, redundant to
> the table's natural key but it does have different characteristics and
> purpose so my dislike for them is not because they add redundant
> information to the model.

You make a valid point here.  I am looking to make a number of changes to my 
systems as I add enhancements and I think that  I will use all of the methods 
mentioned. 

Using reverse() may not be as much use as I first thought because of the AJAX 
style of the user input, but could still be of use on programmatic searching.

Adding a SERIAL as an index for my compound stock would also be of use and 
could be implemented  progressively.

Using the fixed length 11 character VIN field and a trigger has already proved 
it's worth on one table so I think I will expand it's use more to improve 
creating foreign key pairs as different data feeds allow.

Thanks to all replies.

-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk