sqlite3 error

[Steve Holden]

John Machin wrote:
> John Salerno wrote:
> 
>>John Machin wrote:
>>
>>>John Salerno wrote:
>>>
>>>>CREATE TABLE Researchers (
>>>>     researcherID varchar(9) PRIMARY KEY NOT NULL,
>>>>     birthYear int(4) DEFAULT NULL,
>>>>     birthMonth int(2) DEFAULT NULL,
>>>>     birthDay int(2) DEFAULT NULL,
>>>>     birthCountry varchar(50) DEFAULT NULL,
>>>>     birthState char(2) DEFAULT NULL,
>>>>     birthCity varchar(50) DEFAULT NULL,
>>>>     nameFirst varchar(50) NOT NULL,
>>>>     nameLast varchar(50) NOT NULL,
>>>>     nameGiven varchar(255) DEFAULT NULL,
>>>
>>>A bit OT, but one answer to the "can you make a living with just
>>>Python" question is "Yup, tool of choice for rummaging in and fixing
>>>data that's been mangled by users cramming it into dodgy data models"
>>>:-)
>>>
> 
> [snip]
> 
>>>(2) It's not apparent how you would use the first, last, and given
>>>names columns, especially with two of them being "not null". Consider
>>>how you would store e.g.:
>>>J Edgar Hoover
>>>DJ Delorie
>>>Sukarno
>>>35
>>>Maggie Cheung Man-Yuk
>>>Molnar Janos
>>>Fatimah binte Rahman
>>>Zhang Manyu
>>
>>Not sure I follow you on some of these examples. In the case of J Edgar
>>Hoover, I would just put the full name for his first name, or if "J" is
>>entered, then just that, I suppose. Given name will be first name +
>>middle name(s). (I took most of this schema from a baseball database, so
>>maybe it's just something that worked better there.)
> 
> 
> You sure to be using "last name" to mean "surname" in the sense of
> unchanging family name e.g. John and Mary Smith are the children of
> Fred Smith, who is the son of Joe Smith.
> 
> You would address a letter to Mary as "Dear Ms Smith", and in follow-on
> paragraphs in (say) a news article might refer to her using only the
> surname  e.g. "Smith also announced ...".
> 
> The problems are (1) there are cultures that don't have the concept of
> a surname, and if they do, it may not be the "last name" (2) the name
> may consist of only one word (giving you problems with "not null") or
> not even be a word.
> 
> DJ Delorie -- his "given name" *is* DJ
> Sukarno -- one word name
> 35 -- some jurisdictions allow name to include (or consist solely of)
> digits
> Maggie Cheung Man-Yuk -- surname is Cheung [Hong Kong romanisation]
> Molnar Janos -- surname is Molnar
> Fatimah binte Rahman -- no surname; daughter of person whose given name
> is Rahman
> Zhang Manyu -- surname is Zhang [pinyin romanisation]; same person as
> Maggie Cheung Man-Yuk
> 
> It gets better:
> 
> Iceland: Jon Bjornsson and Hildur Bjornsdottir are the children of
> Bjorn Eiriksson.
> 
> Portuguese: Jose Carlos Fernandes [mothers's family name] Rodrigues
> [fathers's family name]; first step in shortening gives Jose Carlos
> Rodrigues -- no drama here, but compare with Spanish: Jose Carlos
> Fernandez [father's family name] Rodriguez [mother's family name],
> shortened to Jose Carlos Fernandez.
> 
> [parts of] Somalia, Ethiopia: [using English given-name words for
> clarity] Tom Dick Harry and Janet Dick Harry are the children of Dick
> Harry Fred, who is the son of Harry Fred Joe.
> 
> Vietnamese: Full name e.g. Nguyen Thi Hoa Dung -- I have seen this
> recorded as last name = Nguyen (that's the family name; doing well so
> far), but first name = Thi. Thi means "Ms" or "female". The "first
> name" is actually Dung. Given the popularity of Nguyen as a family name
> (about 50% !!) , the recorded information has narrowed the choice to
> about 25% of the Vietnamese population :-(
> 
While I don't dispute any of this erudite display of esoteric 
nomenclature wisdom the fact remains that many (predominantly Western) 
databases do tend to use first and last name (in America often with the 
addition of a one- or two-character "middle initial" field).

So, having distilled your knowledge to its essence could you please give 
me some prescriptive advice about what I *should* do? :-)

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC/Ltd          http://www.holdenweb.com
Skype: holdenweb       http://holdenweb.blogspot.com
Recent Ramblings     http://del.icio.us/steve.holden