Hi Rich,
On 7/30/2014 15:04, Rich Shepard wrote:
On Wed, 30 Jul 2014, Werner wrote:
I don't like using 'name' columns as primary keys I would instead use an
'id' column and would set 'index=True' on the name column.
Werner,
The use of natural keys (such as a vehicle VIN, the US's SSN, or
equipment
serial number) is prefered over an artificial, meaningless, integer
key to
prevent duplicate data. See any of Joe Celko's SQL books.
His book looks very interesting.
Over the past 30 or so years I've resorted to artificial keys only when
absolutely necessary. Consider a table for water chemistry constituent
concentrations. There can be no more than one row for the
concentration of a
specified constituent from a distinct location on a given day. The
only way
to ensure this uniqueness is with the compound primary key of (parameter,
sampdate, site). An articial 'id' column fails to prevent duplication
because someone could enter the same laboratory results more than once
and
each row would have a unique 'id' primary key but duplicate data.
I can see the advantage these things, but not sure on 'agency_contacts'.
In the early 1990s I was fired from a database consulting assignment
with
a medical resarch unit because I changed their flat-file database
structure
to a relational schema and turned up duplcate data for a number of
patients.
When you consider the effects on published analyses of data that
contained
duplicate entries, they had to pick a scapegoat and I was it. :-)
Seriously, read Joe Celko's "SQL for Smarties" (I think the 4th
edition is
the latest) for robust DDL practices.
The unicode vs string suggestion is interesting. I'm not sure of the
advantages (or disadvantages) but if the change is neutral I'll run a
global
search-and-replace.
I find it easier to deal with things like 'éüö' etc and IIUC there is no
en/decoding if you use Unicode in the DB and Python.
Werner
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
