Re: Text search language field

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