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

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