Re: Text search language field

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.