Re: Re: Advice - indexing on varchar fields where only last x characters known
От | Gary Stainburn |
---|---|
Тема | Re: Re: Advice - indexing on varchar fields where only last x characters known |
Дата | |
Msg-id | 201310211052.47348.gary.stainburn@ringways.co.uk обсуждение исходный текст |
Ответ на | Re: Advice - indexing on varchar fields where only last x characters known (David Johnston <polobo@yahoo.com>) |
Список | 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
В списке pgsql-sql по дате отправления:
Предыдущее
От: David JohnstonДата:
Сообщение: Re: Advice - indexing on varchar fields where only last x characters known
Следующее
От: skinner@britvault.co.uk (Craig R. Skinner)Дата:
Сообщение: Number of days in a tstzrange?