sqlite3 error

John Salerno johnjsal at NOSPAMgmail.com
Thu Sep 28 10:03:10 EDT 2006

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"
> :-)
> (1) Consider that some countries have states/provinces with 3-letter
> abbreviations (e.g. Australia) or no abbreviation than I'm aware of in
> a Latin alphabet (e.g. China).

Good point. I guess since this program is for my own use, I just figured 
I could stick with the American style abbreviation. But it's not as if I 
actually considered that this wouldn't always work, so it's good to be 
aware of the issue.

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

More information about the Python-list mailing list