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

Re: Text search language field

From: Daniel Staal <DStaal(at)usa(dot)net>
To: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>, PGSQL-Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Text search language field
Date: 2012-05-13 02:56:46
Message-ID: CA9636FAE542DB8BBA4ACD34@mac-pro.magehandbook.com (view raw or flat)
Thread:
Lists: pgsql-novice
--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.

>   • 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.)

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.)

>   • 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.

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.
---------------------------------------------------------------

In response to

Responses

pgsql-novice by date

Next:From: Gavin FlowerDate: 2012-05-13 03:37:22
Subject: Re: Text search language field
Previous:From: Gavin FlowerDate: 2012-05-13 02:15:36
Subject: Re: Text search language field

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