Re: table design strategy

-- Thanks for responding:

-- Regarding the trigger suggestion, I'm not sure
-- I follow (translation: I wouldn't know a trigger from the gun).

-- I'm looking at my PostgreSQL book and looking that
-- feature up ...

-- While I do, what about this:

-- * Append all of the 2000 and 2001 tables into one table
-- * Make index of columns in the one table
-- * For updates and new imports, create a
-- method to only append new data / updates
-- (I imagine this is where the trigger function comes
-- in, right?) when I have to pull data (the source tables
-- will be much smaller ... hopefully ...

-- I have quite a lot to research ... any suggestions
-- will be appreciated.

-- Thanks again!

-X

-----Original Message-----
From: Andrew Sullivan [mailto:andrew(at)libertyrms(dot)info]

> Howdy:
>
> Running Postgres 7.1.3 on RedHat 7.2, kernel 2.4.7 rel. 10.
>
> My question is about table design strategies ...
>
> I have a imported a table, broken up in such a fashion that
> there are now 8 tables on Postgres. For example: table_north,
> table_south, table_east and table_west originally comes from
> another source on another database called 'table_direction'.
> >From that, the tables span years, so, I have tables called
> 'table_north_2000' and 'table_north_2001', 'table_south_2000'
> and table_south_2001' and so on ...
>
> Now ... I was thinking that now that I have all 8 parts, I'd like
> to:
>
> * create indices on the similar names in each table
> * create a view that joins all 8 tables into ONE table again

That view is going to be _painful_ in performance. What if you wrote
a trigger that inserts/deletes/updates in table_all when any of the
other tables are touched? It's a lot of disk, but disk is cheap.

> PS: Has anyone had a chance to test a Data Model / database
> structure modeling tool (for creating pretty pictures and relational
> info / documentation)?

DbVisualiser turns out to be much better than I expected. You can
also get a program called postgres_autodoc.pl if you just want to
create pretty UML diagrams. The latter requires dia, the former Java
2. (I don't have references handy, but a google search should get
you there.)

A