Skip site navigation (1) Skip section navigation (2)

Re: Text search language field

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: PGSQL-Novice <pgsql-novice(at)postgresql(dot)org>
Cc: Daniel Staal <DStaal(at)usa(dot)net>
Subject: Re: Text search language field
Date: 2012-05-13 09:05:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On 13/05/12 15:37, Gavin Flower wrote:
> On 13/05/12 14:56, Daniel Staal wrote:
>> --As of May 13, 2012 2:15:36 PM +1200, Gavin Flower is alleged to 
>> have said:
>>> I can't comment on how to implement the functionality you want, but I
>>> have a few comments you may (or may not!) find useful.
>> This of course was not the *complete* specs of the tables.  I didn't 
>> feel the need to post pages of SQL for a simple question.  ;)  I just 
>> put in the relevant fields.
>>>   • Field names need not be in double quotes.
>> I know.  But I prefer to quote everything always, both to help them 
>> stand out in the text, and to make sure I don't have any case issues.
>>>   • if a character field is Always 3 characters, then say so
>> It currently is always 3 characters, but I don't want to be to 
>> dogmatic about it, in case that needs to change in the future.  (ISO 
>> codes have done that in the past, and I may decide a 
>> different/additional set of codes is needed at some point in the 
>> future.)  Besides, there is no performance benefit in Postgres.  (The 
>> opposite, actually...)
>>>   • add NOT NULL where appropriate (you may well decide more fields
>>> aught to be NOT NULL)
>> Generally I have, though not always I'll admit.  'Primary key' 
>> implies it, and that was the only one that actually has that 
>> restriction in the selection of fields I showed.
> I am amazed at the number of times I see people specifying 'NOT NULL' 
> and PRIMARY KEY' for the same field!  Mind you, these same people 
> could probably justifiable laugh at the daft things I do!  :-)
>>>   • keeping primary and foreign key fields separate from user visible
>>> data.
>> I disagree strongly here.  ;)  Primary keys should be whatever is 
>> suitable for the data; creating an artificial primary key (when not 
>> needed) is just storing more data and doing more work.  It also gives 
>> a feeling of 'safety' which doesn't actually exist, as you can then 
>> create data that is invalid, but that fits because your table design 
>> allows it.  In particular, the language table keeping the the 'code' 
>> unique and distinct is the *entire point* of the table, so there is 
>> no good reason to use anything else as the primary key.  (I do have a 
>> generated ID in the resource table, though it's a much more complex 
>> generation than a simple serial.  Again, I didn't feel the need to 
>> show it.)
> Actually, If I had thought about it a bit more, and something I would 
> (or should!) do if I was designing the table for real, would be to use 
> an UNIQUE qualifier for the code field.
> Unless there are performance and/or data storage, or some such 
> constraints - I prefer to linking tables with user visible things  One 
> production database I worked on had 5 tables in a chain of 
> parent/child relationships, and each child primary key was a 
> concatenation of a character field with the fields comprising the 
> primary key of its parent – could be over 45 bytes in characters in 
> length!
hmm... I meant >>> linking tables with _NON_ user visible things <<<
(probably obvious, but just in case...)
> The current database I am designing is very complicated, but likely 
> never to have more than a few thousand records in any table, and is 
> likely to have many more reads (with some quite complicated queries) 
> than writes. So I focus on trying to work in a very standardizing way, 
> without having to worry over much about performance. Knowing my luck, 
> my next project will be the exact opposite!
>> This does mean thinking through your future use-cases a bit more at 
>> the initial design stage, but if I wasn't willing to do that I 
>> wouldn't be asking this question in the first place.
>> (Actually, the languages table is nearly exclusively used inside the 
>> database, so you could claim it *is* separate from user visible 
>> data.  I could almost use an enum there instead, but having a linked 
>> description available might be useful on occasion.  I also thought 
>> that knowing it exists might be useful in solving my problem: One 
>> possible issue is that text is not the correct data type, and having 
>> a mapping table available might be useful.)
> I would like to use ENUMs in postgres, but there appear to be problems 
> f they need to be updated.
>>>   • identifying primary and foreign key fields clearly
>> Agreed, although I only showed one of each.  ;)
>>>   • suggest table names be singular (my convention, not universally
>>> adopted!)
>> I tend to use plural or singular depending on how they will be used: 
>> 'resource' will tend to be used one at a time, while 'languages' will 
>> tend to be used as a reference list, and therefore as an aggregate.  
>> A more defined naming scheme might be useful I'll admit.
> Horses for courses.
> I remember many years ago, that there was a big argument about 
> systematic as distinct from meaningful names. I was programming in 
> COBOL (names could be up to 30 characters long), I thought the 
> argument was silly, as it depends... In fact in one COBOL program I 
> adopted both approaches, as short systematic names are better for use 
> in a set complicated numerical expressions and only in a self 
> contained stretch of code, and meaningful names for variables used 
> throughout a program.
> For one stored procedure (TransactSQL) of over 3000 lines, accessing 
> 15 tables using 7 temporary tables – I was very carefully in the 
> systematic use of suffixes and consistent use of names.
>> Daniel T. Staal
>> ---------------------------------------------------------------
>> This email copyright the author.  Unless otherwise noted, you
>> are expressly allowed to retransmit, quote, or otherwise use
>> the contents for non-commercial purposes.  This copyright will
>> expire 5 years after the author's death, or in 30 years,
>> whichever is longer, unless such a period is in excess of
>> local copyright law.
>> ---------------------------------------------------------------
> Never too sure what other people know, being helpful can run the risk 
> of seemingly be patronising!  I remember in one job I was given a task 
> whee for part of it I was effectivly at the level of a trainess, and 
> for other parts i had greater experience - a little unsettling!
> Cheers,
> P.S. about top posting before, I got a bit distracted by a work 
> related call.

In response to

pgsql-novice by date

Next:From: Daniel StaalDate: 2012-05-13 17:32:34
Subject: Re: Text search language field
Previous:From: Gavin FlowerDate: 2012-05-13 03:37:22
Subject: Re: Text search language field

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group