-- 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@libertyrms.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