I'm switching to PostgreSQL from MySQL. Using the SAMs book called PostgreSQL which has been great to skim the surface of the differerences.
I had never even heard of things like triggers, views, and foreign keys before.
Any recommended books or websites (or exercises) that would really help someone get to know not just the basics of how these advanced features work, but some real in-depth insight into how to USE them for real work?
I'd start out with:
http://www.acm.org/classics/nov95/toc.html
Unfortunately, the ACM doesn't have the complete paper online.
Then read:
C.J. Date, An Introduction to Database Systems
Or skip over the Intro book and read:
C.J. Date, A Guide to the SQL Standard
Here's a good link for problems caused due to lack of normalization:
http://209.197.234.36/db/simple.html
You'll see that views and foreign keys are fundamental to ensuring consistency and handling data with normalized base tables. You should try to achieve logical consistency in your design without using triggers through the use of domain constraints, column and table constraints and referential integrity constraints. Failing to enforce consistency at that point, triggers can be used to enforce such things as what Date calls database constraints. I.e.: if a department has a budget of under 1000, there should not exist more than 5 employees.
Hope that helps,
Mike Mascari
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
