[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
Re: Fresh database schema
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
>: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> <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> <
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