Re: Fresh database schema

["Ilya A. Volynets-Evenbakh" <ilya@total-knowledge.com>]

Any reason not to make it NULL instead?

Alexey Parshin wrote:
> If we don't know anything about city - that corresponds to city id =
> 1, BTW
>
> 2007/3/5, Ilya A. Volynets-Evenbakh <ilya@total-knowledge.com
> <mailto:ilya@total-knowledge.com>>:
>
>     And don't forget about the fact that all locality data is optional.
>
>
>     Alexey Parshin wrote:
>     > AFAIK, when we deal with some local people, a lot of them come from
>     > relatively short list of cities/suburbans. In any case, we
>     should gain
>     > if the average number of people per city is 2 or greater. And since
>     > we're talking about city+country, that gain may be significant.
>     >
>     > 2007/3/5, sergey@total-knowledge.com
>     <mailto:sergey@total-knowledge.com>
>     > <mailto: sergey@total-knowledge.com
>     <mailto:sergey@total-knowledge.com>> <sergey@total-knowledge.com
>     <mailto:sergey@total-knowledge.com>
>     > <mailto:sergey@total-knowledge.com
>     <mailto:sergey@total-knowledge.com>>>:
>     >
>     >     Ok, understood.
>     >     Not sure about city_list, it's just too many cities in the
>     world. That
>     >     would work with country_list though.
>     >
>     >     > 1) Use number of fields from the stored proc definition in
>     .sql
>     >     file. I
>     >     > will
>     >     > update the schema later.
>     >     > 2) City is the city_id from city_list table. You can
>     select the
>     >     existing
>     >     > city, or create new one with city_create() proc, or I can
>     add a
>     >     stored
>     >     > proc
>     >     > that does it for you
>     >     > 3) I didn't enforce much of security yet. At the moment, it
>     >     makes your
>     >     > life
>     >     > easier. It would be tightened pretty soon.
>     >     >
>     >     > 2007/3/5, sergey@total-knowledge.com
>     <mailto:sergey@total-knowledge.com>
>     >     <mailto:sergey@total-knowledge.com
>     <mailto:sergey@total-knowledge.com>> < sergey@total-knowledge.com
>     <mailto:sergey@total-knowledge.com>
>     >     <mailto:sergey@total-knowledge.com
>     <mailto:sergey@total-knowledge.com>>>:
>     >     >>
>     >     >> I started using stored procedures in my code and I have some
>     >     problems
>     >     >> with
>     >     >> them.
>     >     >> First of all, I pulled the latest version of DB *.sql scripts
>     >     from svn.
>     >     >> In trunk/db/procs/person_list.sql defined person_create
>     >     procedure which
>     >     >> I'm trying to use for registration functionality.
>     >     >> So here is the list of problems, all imho:
>     >     >>
>     >     >> 1. Number of fields in person_create does not match
>     number of
>     >     fields in
>     >     >> person_list table(pl_login, pl_password,
>     pl_preferred_language,
>     >     >> pl_country, pl_state are missing)
>     >     >> 2. pl_city field should be varchar, not int.
>     >     >> 3. No error handling: I was able to make an insert with empty
>     >     pl_login
>     >     >> field.
>     >     >>
>     >     >> Also I see that person_create procedure returns
>     lastval(). Is it
>     >     >> possible
>     >     >> to return boolean value based on successful/unsuccessful work
>     >     of stored
>     >     >> procedure?
>     >     >>
>     >     >>
>     >     >>
>     >     >>
>     >     >>
>     >     >
>     >     >
>     >     > --
>     >     > Alexey Parshin,
>     >     > http://www.sptk.net
>     >     >
>     >
>     >
>     >
>     >
>     >
>     > --
>     > Alexey Parshin,
>     > http://www.sptk.net
>
>     --
>     Ilya A. Volynets-Evenbakh
>     Total Knowledge. CTO
>     http://www.total-knowledge.com
>
>
>
>
> -- 
> Alexey Parshin,
> http://www.sptk.net 

-- 
Ilya A. Volynets-Evenbakh
Total Knowledge. CTO
http://www.total-knowledge.com