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