X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4A4499FB1D7 for ; Sat, 8 Jul 2006 12:53:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 98866-02 for ; Sat, 8 Jul 2006 12:53:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from dougal.cranntara.net (unknown [217.112.91.201]) by postgresql.org (Postfix) with ESMTP id C7E359FB1D3 for ; Sat, 8 Jul 2006 12:53:17 -0300 (ADT) Received: from host217-32-121-229.webport.bt.net ([217.32.121.229] helo=bolek.coachhouse) by dougal.cranntara.net with asmtp (Exim 3.36 #1 (Debian)) id 1FzF8f-0004bT-00; Sat, 08 Jul 2006 16:54:38 +0100 Received: (Exim 3.36) #1 (Debian)) protocol: esmtp id 1FzEum-0005xU-00 ; Sat, 08 Jul 2006 16:40:16 +0100 Message-ID: <44AFD1DF.8010102@numerixtechnology.de> Date: Sat, 08 Jul 2006 16:40:15 +0100 From: T E Schmitz Reply-To: mailreg@numerixtechnology.de User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Aaron Bono CC: pgsql-sql@postgresql.org Subject: Re: SELECT substring with regex References: <44AE66E0.1040606@numerixtechnology.de> <200607071659.14940.gary.stainburn@ringways.co.uk> <44AE9B4F.1020401@numerixtechnology.de> <44AEAED9.7080300@myemma.com> <44AEC8C5.80103@numerixtechnology.de> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/135 X-Sequence-Number: 25709 Aaron Bono wrote: > On 7/7/06, *T E Schmitz* > wrote: > > > I am trying to come up with a semi-automatic solution to tidy up some > data. If it's got to be done manually via the GUI it would mean a lot of > dummy work [for the customer]. > > > I would recommend you alter the GUI to NOT allow any more bad data to I absolutely agree that the user should not be able to enter 'bad' data in the first place. However, in this case it is not a hard and fast rule, rather a suggestion how to organize the data sensibly. The pattern is suitable for 98% of the lenses; there are some, albeit very few, exceptions. Also: the MODEL.NAME is a free-form field; only when referred to by a PRODUCT which is of CATEGORY 'Lens', would the pattern apply. The only way one could maybe assist the user would be a warning when he tries to save a 'Lens' PRODUCT, which deviates from the pattern. > You might also consider adding the base and suffix columns with a > trigger that parses the name field and sets the values of base and > suffix and also does any integrity checks during inserts and updates. > Finally, after the data is cleaned up and the client apps are changed to > use base and suffix and not name, get rid of the name column. The suffix should have been entered in TYPE.NAME, which references MODEL. The structure exists, it just needs to be used efficiently. -- Regards, Tarlika