Re: Text search language field

--As of May 13, 2012 3:37:22 PM +1200, Gavin Flower is alleged to have said:

>> 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!  :-)

I don't mind the belt-and-suspenders approach on this, if you want to use
it.  Sure, it's redundant, but it's also clearer and a decent reminder to
the human reading the SQL.

>>>   • 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 (non)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 project has the potential to be very performance-critical, so I'm
trying to operate on that assumption.  ;)

And again, I don't see the point of creating extra fields and data just to
enforce some artificial separation between 'user-visible' and 'database'
fields.  There's no performance benefit, there's a maintenance *penalty*
(in that your data is more complicated), and a programming penalty.
(Again: your data is more complicated.)

In this case, for example, doing it with a separate 'id' field would either
require a hash lookup in the application this database is being created to
support, or a separate lookup on nearly every write to the resources table
(AKA: The second-most common operation I'm expecting), just to get the
language code id.  The first has obvious maintenance problems, and they
both have a performance penalty.  And none of this has any benefit to
anyone, that I can see.  So why?

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

The only time it's not silly is when it's being decided upon as a
'company-wide standard'.  Then it's tragic.  ;)  (Good guidelines are
invaluable.  Enforced standards, especially given by those who aren't doing
any of the actual work, are not.)

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

That's been most of my jobs.  ;)

> P.S. about top posting before, I got a bit distracted by a work related
> call.

No problem.  Now, if I could only get you to respect the 'Reply-To:'
header...  (I'm also not a fan of HTML email, but I can live with that.)

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