Re: Fresh database schema

["Alexey Parshin" <alexeyp@gmail.com>]

If you have NULL in the city field, then you have to make an outer join that is slower. For this reason, in this database every table that could be referred has the default record with id=1. In ideal case, we should have no outer queries.

2007/3/5, 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

-- 
Alexey Parshin,
http://www.sptk.net