Forgive my ignorance if I'm incorrect but don't you need to do a select on
your cities, zips, etc. to populate the id into you customers data anyway?
Therefore you would already know if you have the city, zip, ....
Also, if you have only a "few" cities, it may be more efficient to store the
city definition which includes the state, zip etc. and only have 1 id to
normalize you data in the customer table. I work for a transportation
company and have over 140,000 cities defined. For speed I'd rather do 1
query to get the city information I need rather than 3 or 4 queries.
On Tuesday 08 October 2002 12:48 pm, you wrote:
> Thanks, doing a SELECT to see if the city (etc.) was there is
> exactly what I am trying to avoid.
>
> Gurus, any comments?
>
> On Tue, 8 Oct 2002, Devinder K Rajput wrote:
> > Josh,
> >
> > I am assuming that data gets entered into the customers table and then
> > should automagically update the cities, states, and zip_codes tables.
> > For example, if a new record or update contains the city "Chicago" and
> > Chicago doesn't exist in cities, it should be added/inserted. There
> > might be a way to do this by setting up integrity constraints ( i don't
> > know how), but you could also do it in code. When a record is
> > inserted/updated in the customers table, do a select with the city value
> > in the cities table to see if the city exists. if the city doesn't
> > exist, insert it. Do the same with states and zip codes. This is some
> > work and like I said, you might able to do it integrity constraints, but
> > we need some help from the postgres gurus to see if that's possible.
> >
> > regards,
> >
> > Devinder Rajput
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
