Re: Text search language field

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!

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.