sqlite3 error

[John Machin]

Steve Holden wrote:
> 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? :-)
>

Yes; the details would depend on the application (movie actor database,
factory payroll, pension fund, social security, homeland security, ...)
and should follow fairly naturally from a requirements analysis. The
main pre-requisite is for both the users and IT to get an attitude
transplant :-)

Cheers,
John

Cheers,
John