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
