Storing/Retrieving DB Records

I'm working on a genealogy program that will use a database to store
the data.  I don't have any previous experience with databases, SQL,
ODBC, etc.  I'm using Microsoft Access.

I'm not sure how to handle the situation involving storing and
retrieving the records with Persona-ID (Primary Key) and Persona-Name
fields in the PERSONA table.  I think defining Persona-ID as an
"AutoNumber" is correct.  I add a new one with:

        s := c exec: 'INSERT INTO PERSONA ("Persona-Name") VALUES (''John
Doe'')'

How do I access the new record I just created as I don't know the
Persona-ID because the database generated it.  Presumably the first
new record will be (1, 'John Doe'), but I can't search the table for
'John Doe' as there may be duplicate names which was the purpose of
the Persona-ID in the first place.

I've thought I might define Persona-ID as a "Number" and just keep
track of the next new unique id myself in Smalltalk code, but this
seems problematic.

I suspect this is a common situation but how is it normally handled in
Smalltalk?


My second, minor, question is where to look to find the correct syntax
to specify references to field names that contain special characters
other than letters (ie space, -, etc.).  I found by experimentation
that double quotes (") works:

        rs := c query: 'select * from PERSONA'.
        rs collect: [:x | x "Persona-ID"].

The double quotes (") just looks wrong/odd/something.  I think the
character for enclosing field names containing special characters
depends on the database management engine (Jet? for MS Access), but
not sure.


I'd just note that the logical Genealogy data model I'm using is
already defined by a committee of folks in the genealogy field and
genealogical program vendors.  I want to stick with their naming
scheme for tables and fields.

I'm really feeling my way forward in learning to use databases, so any
help would be appreciated.  Any pointers to where to look for answers
to questions would help.


--
Richard A. Harmon          "The only good zombie is a dead zombie"
[hidden email]           E. G. McCarthy

Richard,

> I'm working on a genealogy program that will use a database to store
> the data.  I don't have any previous experience with databases, SQL,
> ODBC, etc.  I'm using Microsoft Access.

Access is a nice choice for the DBMS.  If the user interface to a database
is simple, it can be done directly in Access.  Things that would push you
toward a non-Access GUI include:  (1) large numbers of fields to be
displayed or an otherwise complex GUI; (2) computation that doesn't fit well
in SQL queries; (3) plans to widely distribute the app.


> I'm not sure how to handle the situation involving storing and
> retrieving the records with Persona-ID (Primary Key) and Persona-Name
> fields in the PERSONA table.  I think defining Persona-ID as an

Are you in control of the database design?  If so, I'd recommend that you
change the field names.  I've had very good luck with naming fields
LIKE_THIS.  Of course, Squeak might give you grief about it, but, then I
haven't gotten that far with Squeak.  I mention Squeak because I now know
from experience that the SIF works (thanks!!) and you might have plans to
use it.


> "AutoNumber" is correct.  I add a new one with:

FWIW, I've reached the point of avoiding AutoNumber at all costs.


> s := c exec: 'INSERT INTO PERSONA ("Persona-Name") VALUES (''John
> Doe'')'
>
> How do I access the new record I just created as I don't know the
> Persona-ID because the database generated it.  Presumably the first
> new record will be (1, 'John Doe'), but I can't search the table for
> 'John Doe' as there may be duplicate names which was the purpose of
> the Persona-ID in the first place.

First, please note that I am NOT a database expert, so I could be selling a
bridge that's under water to enable you to chase a while aquatic fowl.  With
that said, here are few things to think about:

(1) you can create a composite key (one that spans multiple fields) so that
multiple items become necessary to uniquely identify a record
(2) you can do a query to get the max record ID, increment, and then insert
a new record with that ID; you might need to use transactions or views to
make this work correctly in some situations
(3) you will find that you'll have to pay some attention to whether a record
already exists; query, if the results are empty then insert else update (you
probably already knew that)


> I've thought I might define Persona-ID as a "Number" and just keep
> track of the next new unique id myself in Smalltalk code, but this
> seems problematic.

In my experience, not as problematic as auto-numbering.


> I suspect this is a common situation

Definitely.


> but how is it normally handled in
> Smalltalk?

When you find out, please let me know :)


> My second, minor, question is where to look to find the correct syntax
> to specify references to field names that contain special characters
> other than letters (ie space, -, etc.).

With respect, you'll have less trouble if you avoid those characters.  I've
done well with underscores, but, I'm aware that I'd have trouble in Squeak
down stream.  However, I _suspect_ that if I ever had to do that much with
Squeak, it would be in response to a major screw up by MS (charging runtime
fees, dropping support for everything but Windows OE (or else), etc.) and
that various hassles in Squeak would be fixed by my fellow (future)
refugees.


If you consider the Windows Installer to be a DBMS, then you'll find ` and '
used for the purpose.  One of the things that I particularly enjoy is that
the installer uses field names that stomp on reserved words, so one _must_
use these characters eventually; since they have/had control over
everything, I think it was a very poor choice to release it that state.

Access seems fairly forgiving about single vs. double quotes.  Another thing
to remember: you can use Access' query editor to drag and drop and set all
kinds of sorting/aggregation/etc options, and then hit the SQL button to
have the ugly stuff done for you.


> I'd just note that the logical Genealogy data model I'm using is
> already defined by a committee of folks in the genealogy field and
> genealogical program vendors.  I want to stick with their naming
> scheme for tables and fields.

In that case, use the SQL view in Access' query editor.


> I'm really feeling my way forward in learning to use databases, so any
> help would be appreciated.  Any pointers to where to look for answers
> to questions would help.

Beware going too far with normalization.  There are people who will spend
their entire careers thinking about such things, and I'm grateful for their
efforts, but, it can really get in the way for simpler problems.  There are
also flexibility/performance tradeoffs to consider.  My hunch (note that I
don't live in Bill Gates' neighborhood) is that you'll do better to build
things that work and let them get bought out by deeper pockets that can
afford to labor over such things.

SQL for Dummies is an excellent book.

Hope this helps!

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

In article <[hidden email]>, [hidden email] says...
[snip]

>
>I suspect this is a common situation but how is it normally handled in
>Smalltalk?

Richard,

The problem you are experiencing has little if anything to do with
Smalltalk and everything to do with your database design.  You do
need to normalize your model, not only to make it easier to retrieve
things, but also to allow you to know where to update when it needs
to be updated.  

The trouble -- and the feature, in some situations -- with auto-generated
keys is that they have no external counterpart.  Thus, if you have
several "John Doe"s in your database, using an autonumber or auto-generated
key doesn't really solve anything.  You will still need to devise a
means of knowing which John Doe is meant when an external source
mentions him.  In general, you'll need to use other distinguishing
information about John Doe, such as a national identifier (unlikely to
be available), an address (still possibly ambiguous), a phone number,
or an e-mail address.  Birthdates are usually good discriminators.

This is a non-trivial problem and, indeed, there are some so-called
"data cleansing" companies that make a living doing this on a large
scale for businesses.  I faced it in a custom design for a professor
who was trying to track the political affiliations of various members
of the American Party (a.k.a., the "Know Nothing Party") in the State of
Rhode Island in the U.S. during the 1850-1865 period.  It resulted from
the historical fact that the children of these folks burned their diaries,
possibly having something to do with their tendency to eliminate political
rivals and especially new immigrants using violence.  Anyway, we tried
tracking these folks by their names -- sometimes only first initials -- listed
on rosters of attendees at legislative sessions (with party affiliation
required to be stated), party caucuses, and party conventions.  As it turned
out, our results were intended to be statistical, giving trends, so if
we were off by one or two common names, it did not matter on a base
counting in the hundreds or thousands.  You may have other criteria
which may make this approach entirely unsatisfactory.

But the situation is not at all hopeless.  For instance, even if you need
to retrieve information based upon homonyms, there are procedures and
algorithms which can support that.  You are lucky that you have chosen
Smalltalk as your means to realize this, for a good deal of experimentation
will probably be needed and there's no better tool for doing that.

While the database stuff is not at all difficult to learn, you still
need to invest the time to learn it.  I recommend two good books here.

A very good one is Bowman's PRACTICAL SQL HANDBOOK, described at

    http://www.bookpool.com/.x/o7yw2spp1m/ss/1?qs=0201447878&Go.x=16&Go.y=11

A more basic approach, but still very good, is Pratt's A GUIDE TO SQL,
described at

   
http://shop.barnesandnoble.com/textbooks/booksearch/isbninquiry.asp?userid=2O8SG46V3A&msc
ssid=C5EA08CV3PDT8KHMXPHPTF7AG9XM6EHA&isbn=0619033827

I prefer using bookpool.com to order technical texts, because they are
often cheaper, but their availability isn't as good as bn.com or
amazon.com.  Bowman's book has an ISBN of 0201447878 and Pratt's book's
ISBN is 0619033827.  They are both in stock at bn.com.

I understand you mentioned that the geneaology folks have their own
logical models for things, but you only need to concern yourself with
those when you exchange information with others using that standard.
There's nothing to prevent you -- and indeed this is how things are
supposed to be -- from devising your own tables realizing your own
logical model for your own purposes.  These then coexist with the
table or tables built conforming to the model provided by the
geneaology folks.  You write things that move data between them.


Well, using these characters as parts of field names is not, in general,
a good idea.  The reason is that as your database grows in size, you may
want to, someday, move it from Access to something else. These kinds of
usages make that transition more work than it needs to be.  You should
also limit field or column names to something 18 characters or less long.

The documentation at

  http://www.object-arts.com/Lib/EducationCentre4/htm/databaseconnectivity.htm

and in the Education Centre that is part of your Dolphin system is pretty
good.  Most databases let you and the Dolphin Smalltalk system access
their schema structure, as described in

  http://www.object-arts.com/Lib/EducationCentre4/htm/queryingthedatabasestructure.htm

In general, just like record structures are accessed in Smalltalk using
getter messages or methods, the database interface defines accessor methods
which have names coincident with field names in the database's table records.
Obviously, if the name of the field or column can't be a legitimate Smalltalk
message name, you have a problem.  You can, of course, access it by position,
using the at: message.

Please feel free to drop questions to me about this Smalltalk and database
business.  I spend a lot of time living in those spaces.

[snip]

Best of luck

  --jtg

--
---------------------------------------------------------------------
 Jan Theodore Galkowski                    [hidden email]
 The Smalltalk Idiom                             [hidden email]
*********************************************************************
     Ask me about the Disney Vacation Club!  See algebraist.com/
*********************************************************************
             "Smalltalk?  Yes, it's really that slick."
---------------------------------------------------------------------
Want to know more?  Check out
           http://www.dnsmith.com/SmallFAQ/
           http://www.object-arts.com/DolphinWhitePaper.htm
           http://st-www.cs.uiuc.edu/users/johnson/smalltalk/
*********************************************************************

On Sun, 1 Jul 2001 13:59:03 -0400, "Bill Schwab" <[hidden email]>
wrote:

It has about 35 tables with approximately (guess) 5 fields each?  The
committee that developed the logical Genealogy data model (GDM)
conceded this was much too complicated to use the user interface to
enter data in the database.  This GDM is intended as an industrial
strength specification suitable for professional level genealogical
research and in commercial genealogy programs.

Listed under the FAQ point "Why is the model so complicated?"

"In addition, the data model is complicated because genealogical data
not only spans an enormous variety of record types, it also involves
data that must always be viewed with skepticism.  A typical business
application may have a lot of different kinds of data types, but
seldom is the business data model designed to purposefully accept data
that later will be proved to lead to incorrect conclusions."

And another line that went something like "... powerful computer
programs will pull the data pieces together into simpler views the
genealogist will want to see ...."

If that isn't an irresistible siren song to a Smalltalker to try a run
at the rocks riding the Smalltalk balloon/dolphin, I'll eat my
keyboard.

I think almost all genealogy applications/utilities will be written in
C, C++, VisualBasic, Java, etc.  I think Smalltalk can beat the pants
off these in its power and ease to create views of genealogical data.
 

>> I'm not sure how to handle the situation involving storing and
>> retrieving the records with Persona-ID (Primary Key) and Persona-Name
>> fields in the PERSONA table.  I think defining Persona-ID as an
>
>Are you in control of the database design?  If so, I'd recommend that you
>change the field names.

I knew someone would suggest this.  I prefer to use the names in the
GDM specification for a variety of reasons one of which is I assume my
applications will need to access existing databases created by other
genealogy programs.  This may later prove to be a poor assumption.

I want to try to create a generic Smalltalk Genealogy Data Model
module/subsystem that can be used by any genealogy application or
utility.  Something sort of on the lines of of the TreeModel.  I think
it is worthwhile to try to follow the GDM specification document as
closely as possible at this point.


>  I've had very good luck with naming fields
>LIKE_THIS.  Of course, Squeak might give you grief about it, but, then I
>haven't gotten that far with Squeak.

Very important point, thanks.  I lurk on a mailing list of a group of
folks writing a genealogy program for UNIX as they couldn't find one
they liked available on that OS.  They are using C++.  Squeak might
very well be a good route to port anything useful I come up with to
UNIX.


>I mention Squeak because I now know
>from experience that the SIF works (thanks!!) and you might have plans to
>use it.

Glad the SIF worked out for you.  Works for me.  Maybe we're finally
moving toward less painful portability across dialects.


>> "AutoNumber" is correct.  I add a new one with:
>
>FWIW, I've reached the point of avoiding AutoNumber at all costs.

I got it exactly backward.  I thought creating the unique number
myself was the wild goose chase.  I appreciate the hearing about your
experience.  I think it will save me effort in not going too far down
a path that is unlikely to succeed.


Good advice.  It was my initial solution also.  Unfortunately the
background material on databases in the specification of the GDM
warned against it as experience tends to show any scheme will
eventually hit a match on the composite keys.


>(2) you can do a query to get the max record ID, increment, and then insert
>a new record with that ID; you might need to use transactions or views to
>make this work correctly in some situations
>(3) you will find that you'll have to pay some attention to whether a record
>already exists; query, if the results are empty then insert else update (you
>probably already knew that)

That is a lot simpler and safer that anything I thought of.  Thanks.
Just a starting place moves me well along the process.


I'll give it a try.

>  One of the things that I particularly enjoy is that
>the installer uses field names that stomp on reserved words, so one _must_
>use these characters eventually; since they have/had control over
>everything, I think it was a very poor choice to release it that state.

Thanks for the warning.


Ah!  Thanks.


The committee of folks that developed the logical GDM already have
normalized it to the 3rd normal form.  I'll leave well enough alone.

I'm really quite impressed with the quality of the effort these folks
put in to it.  I see how well they have address some issues I'd been
struggling with and their solutions seem very sound.


>SQL for Dummies is an excellent book.

You sure this doesn't assume too much knowledge on my part?  Is there
a "SQL for the Totally Clueless Dummies"?
 

>Hope this helps!

It does.  Just knowing I'm not heading off in the opposite wrong
direction helps.  I think I can now move along in my experiments and
nudge the project along.

I've really been pleased with how easy, and especially how forgiving,
Dolphin is to experiment and incrementally develop database stuff.  It
is also just plain fun.


--
Richard A. Harmon          "The only good zombie is a dead zombie"
[hidden email]           E. G. McCarthy

On 1 Jul 2001 23:00:21 GMT, [hidden email] (Jan Theodore Galkowski)
wrote:

The committee of folks that developed the logical GDM already have
normalized it to the 3rd normal form.  I'll leave well enough alone.

At least until I have a little better idea of what I'm doing and read
up on the 4th & 5th normalizations.


>The trouble -- and the feature, in some situations -- with auto-generated
>keys is that they have no external counterpart.  Thus, if you have
>several "John Doe"s in your database, using an autonumber or auto-generated
>key doesn't really solve anything.  You will still need to devise a
>means of knowing which John Doe is meant when an external source
>mentions him.  In general, you'll need to use other distinguishing
>information about John Doe, such as a national identifier (unlikely to
>be available), an address (still possibly ambiguous), a phone number,
>or an e-mail address.  Birthdates are usually good discriminators.

A previous response suggested a solution I'll try -- generate the key
myself.  I always know when I have a new person.  The committee of
folks that developed the logical GDM added an interesting wrinkle in
the situation where the only fact known is that there was a person--no
other information.


Now there is a combination that appeals to me--programming and
politics.  That is an interesting problem.


>But the situation is not at all hopeless.  For instance, even if you need
>to retrieve information based upon homonyms, there are procedures and
>algorithms which can support that.  You are lucky that you have chosen
>Smalltalk as your means to realize this, for a good deal of experimentation
>will probably be needed and there's no better tool for doing that.

That's what I'm betting on.  I started the first version of my
genealogy program in C++ while I was initially exploring Smalltalk.  I
waffled back and forth between them mainly as there weren't any
Smalltalkers I could find.  I thought I try prototyping it in
Smalltalk and move it to C++ after I got the issues sorted out.  I
ended up staying with Smalltalk and never wanted to venture back into
C++ if I could help it.


>While the database stuff is not at all difficult to learn, you still
>need to invest the time to learn it.  I recommend two good books here.
[snip]

Thanks.  I've always found I'm ahead in the book game when I start
with those others have found useful.


>I understand you mentioned that the geneaology folks have their own
>logical models for things, but you only need to concern yourself with
>those when you exchange information with others using that standard.

In genealogy folks have settled on GEDCOM format.  There are
unfortunately a bunch of interesting issues around it that prompted
the creation of the GDM.


>There's nothing to prevent you -- and indeed this is how things are
>supposed to be -- from devising your own tables realizing your own
>logical model for your own purposes.  These then coexist with the
>table or tables built conforming to the model provided by the
>geneaology folks.  You write things that move data between them.

Ah.  Never thought of that.  Thanks.


Important point to me.  I do want to be able to use different
databases.  The GDM is currently out in a Request For Comment? (RFC).
I may want to bring up the field naming scheme with them.  I
understand (I think) why they used the one they did as I think they
use C and C++ almost exclusively (guessing).


>The documentation at
>
>  http://www.object-arts.com/Lib/EducationCentre4/htm/databaseconnectivity.htm

Thanks.  It will take a while for me to figure out what is available
and where it is.  I'll take a look.  I appreciate that the Dolphin
documentation is well written and understandable.


>and in the Education Centre that is part of your Dolphin system is pretty
>good.  Most databases let you and the Dolphin Smalltalk system access
>their schema structure, as described in
>
>  http://www.object-arts.com/Lib/EducationCentre4/htm/queryingthedatabasestructure.htm

I hadn't found this yet, but accessing the schema was a question
already at the top of my list.


>In general, just like record structures are accessed in Smalltalk using
>getter messages or methods, the database interface defines accessor methods
>which have names coincident with field names in the database's table records.
>Obviously, if the name of the field or column can't be a legitimate Smalltalk
>message name, you have a problem.  You can, of course, access it by position,
>using the at: message.

Wrapping the database stuff up as reasonable Smalltalk objects is
probably going to be one of the more difficult steps, especially since
I don't think I've fully incorporated object thinking in my Smalltalk
yet.


>Please feel free to drop questions to me about this Smalltalk and database
>business.  I spend a lot of time living in those spaces.

Thanks for the kind offer, and the help.


--
Richard A. Harmon          "The only good zombie is a dead zombie"
[hidden email]           E. G. McCarthy

"Richard A. Harmon" wrote:

> I'm working on a genealogy program that will use a database to store
> the data.  I don't have any previous experience with databases, SQL,
> ODBC, etc.  I'm using Microsoft Access.

Regardless of language you are using go to google.com and do a search on SQL
and ESQL. Lots of info plus tutorials. (I'm currently switching to using MS
Access as my repository for data - but access to the DB is from my programs
using ESQL, and of course, using the appropriate odbc driver)

Jimmy, Calgary AB

In article <[hidden email]>, [hidden email] says...
>
>On 1 Jul 2001 23:00:21 GMT, [hidden email] (Jan Theodore Galkowski)
>wrote:
>
>>In article <[hidden email]>, [hidden email] says...
>>>
>>>I'm working on a genealogy program that will use a database to store
>>>the data.  I don't have any previous experience with databases, SQL,

[snip]

>
>The committee of folks that developed the logical GDM already have
>normalized it to the 3rd normal form.  I'll leave well enough alone.
>

[snip]


Of course, do whatever you think is best and is most comfortable to
you.  And, yes, it isn't absolutely necessary to put things in normal
form unless you're intending to update the information after you
first load it.  Normal form isn't some merely asthetic thing:  It's
essential so you can readily determine what needs to be updated in
your model when something about the subject changes.

Note that your individual set of subjects and reason for doing
things is very likely to be different than anyone else's set of
subjects and purposes.  Tnus, a logical model they built and
normalized isn't automatically one that fits your situation.
Indeed, it might be far too complicated for your purposes.

But, of course, use and steal whatever you can from others if
it appears to be almost right.  Models don't have to be perfect
to be very useful.

Best of luck, and the offer of help still stands.

  --jtg