Re: one-to-one

Micheal,

> won't apply to many of them. Then I'd use a one-to-one join when I need to
> retrieve both general and membership-specific data. What are the pros and
> cons of this approach?

This is the releational, 3NF approach. I recommend it highly.

Do (NOT real SQL, just an example, consult the docs for accurate syntax)

create table people (
id SERIAL PRIMARY KEY
name
address ....
);

create table member_info (
id INT REFERENCES people(id) PRIMARY KEY
orientation
officer ...
);

Using ID both as PK and as an FK in the member_info table creates a 1:0-1
relationship. Then, through the magic of PostgreSQL rules and views, you can
create a pseudo table called members:

CREATE VIEW members AS SELECT * FROM people JOIN members USING(id)

CREATE RULE member_insert ON INSERT INTO members
DO INSTEAD ( INSERT INTO people (name, address ....)
VALUES (NEW.name, NEW.address, ... );
INSERT INTO members (id, officer, orientation ...)
(currval('people_id_seq'), NEW.officer, NEW.orientation)
;

And do the same for update and delete.
(see "CREATE RULE" in the docs, and this page:
http://www.postgresql.com/docs/7.3/interactive/rules.html)

--
Josh Berkus
Aglio Database Solutions
San Francisco