Monthly Archives: May 2007

How to use compound foreign keys with Postgresql

Posted on by

I am working on a multiple-choice quiz program. We have four tables related to questions and answers:

  • a table of questions
  • a table of answers
  • a table that links each question to multiple answers
  • a table that records a user’s actual answer to a question.

I needed a way to track answers to multiple-choice questions, and to limit possible answers to each question. For example, I want to make sure that if I ask the question “what is your favorite color?” then the answer is a color, and not something silly like “yes.” This is tricky, because “yes” is a valid answer for some questions, but not for others.

This table holds each question.


create table questions (
q_id serial primary key,
q_text text
);

Here are some questions. Since I used the serial data type, I’m not going to assign the q_id column. Postgresql will assign the q_id automatically.


insert into questions (q_text) values ('What is your favorite color?'); /* q_id 1 */
insert into questions (q_text) values ('Is it raining outside?'); /* q_id 2 */

After inserting, this is what the database looks like now:

select * from questions;
q_id | q_text
------+------------------------------
1 | What is your favorite color?
2 | Is it raining outside?
(2 rows)

This table lists every possible answer, each with a unique id.

create table all_answers (
a_id serial primary key,
a_text text
);

These are some answers.

insert into all_answers (a_text) values ('red'); /* a_id 1 */
insert into all_answers (a_text) values ('yes'); /* a_id 2 */
insert into all_answers (a_text) values ('green'); /* a_id 3 */
insert into all_answers (a_text) values ('no'); /* a_id 4 */

Here’s what all_answers looks like after adding data:

select * from all_answers;
a_id | a_text
------+--------
1 | red
2 | yes
3 | green
4 | no
(4 rows)

This table links each question to meaningful possible answers. I am using question ID (q_id) and answer ID (a_id) numbers in order to save space.

create table possible_answers (
q_id integer references questions (q_id),
a_id integer references all_answers (a_id),
primary key (q_id, a_id)
);

Now, I’ll link certain questions with certain answers. This insert links ‘What is your favorite color?’ with ‘red’.

insert into possible_answers (q_id, a_id) values (1, 1);

This statement links ‘Is it raining outside?’ with ‘no’.

insert into possible_answers (q_id, a_id) values (1, 3);

And, just to continue the trend…

insert into possible_answers (q_id, a_id) values (2, 2);
insert into possible_answers (q_id, a_id) values (2, 4);

Now let’s see what we’ve got:

select * from possible_answers;
q_id | a_id
------+------
1 | 1
1 | 3
2 | 2
2 | 4
(4 rows)

Finally, this is the table that will record the actual answer given for a question and make sure that the answer is appropriate for the question.

create table real_answers (
q_id integer references questions (q_id), /* simple FK */
a_id integer references all_answers (a_id), /* simple FK */
foreign key (q_id, a_id) references possible_answers (q_id, a_id) /* compound FK */
);

Now watch what happens when I try to insert an answer that doesn’t match the question into the database. I’m going to try to answer the question “What is your favorite color?” with the answer “yes”.

Hopefully the database will prevent me.

select q_text from questions where q_id = 1;
q_text
------------------------------
What is your favorite color?
(1 row)


select a_text from all_answers where a_id = 2;
a_text
--------
yes
(1 row)

insert into real_answers (q_id, a_id) values (1, 2);
ERROR: $3 referential integrity violation - key referenced from real_answers
not found in possible_answers
Hurray! We are prevented from entering the data! Now, let’s try storing an acceptable answer.

insert into real_answers (q_id, a_id) values (1,1);
INSERT 17196 1

That means it worked!

Oryx and Crake by Margaret Atwood

Posted on by

Summary: a good book about the downfall of civilization written by an author that doesn’t normally write science fiction.

Oryx and Crake is highbrow science-fiction. It’s not hard science fiction, like something written by Clarke or Sagan, where you’ll learn plenty of physics along the way. I mean it’s written by an author with literary credentials. Mainstream critics tend to all say the same thing about this book: “It’s sci-fi, but it’s good,” which is more than a little insulting to the rest of us. That put aside, this is a good book.

Most of the book reads sort of like what you would expect from everything else Atwood writes, despite the futuristic setting: children lose their innocence and discover their parents aren’t saints; love turns to jealousy; admiration turns to hate and fear and people do things they previously never could have imagined. In the end, which is usually where the book starts, the characters try to piece together some meaning out of it all, and grieve their loss.

If I didn’t like this book, I would say it’s sort of like A Separate Peace meets The Omega Man. But I did like this book. However, it’s still some seriously emotional stuff.

Margaret Atwood’s style by this point is well established: her characters change names when their situations change. The book starts off at the end, and the reader discovers what happened when the characters reflect on their lives in thoughts and conversations. Margaret Atwood loves writing about male-female and parent-child dynamics, and the subconscious forces that drive us, and Oryx and Crake is no exception.

I’m not going to run through the entire plot, but the book begins with somebody named Snowman picking through the detritus of western civilization. The plot unwinds while Snowman reflects on his life from childhood until the present. Along the way, we get the story of how the little boy named Jimmy grows up in a world that looks sort of like our near future, and becomes Snowman right after that civilization comes crashing down around him.

In Jimmy/Snowman’s childhood, the world looks vaguely like a more privatized, slightly more technically advanced version of today. As an adult, civilization has collapsed, Snowman is all alone, and he spends his time picking through the wreckage looking for snacks.

I’d wager that at least half of the books on the shelves in the sci-fi section in any bookstore revolve around some sort end-of-the-world scenario, leaving one or a few survivors to make sense out of it all. Atwood dodges the rookie mistake of trying to make her scenario seem plausible, or even worse, mentioning certain years, or real-life politicians. Instead, she goes in the opposite direction. We piece together a picture of the world in the future from snippets of conversation, consumer products, and advertising. We don’t get a detailed roadmap of how we got to a world where corporations run most everything, and their employees live in combination housing developments/office parks/shopping malls.

Crake is Jimmy’s childhood friend; they grow up together in the same private compound where their parents work. They play computer games together, they get high, they watch inordinate amounts of porn (no word on whether Atwood lurked on slashdot to research).

Like a lot of the little aspects of life in this future world, the computer games that Jimmy and Crake play are fleshed out almost to the point that it’s hard to believe these aren’t real games. “War of the Roses” is one game; it seems sort of like Magic The Gathering, or Pokemon, except the mythical monsters are replaced with the highs and lows of humanity. One player can play the Holocaust card, and another player can play the Sistine Chapel card” to negate it. Crake actually gets his name from another game, “Extinctathon”, where the players take turns wiping out species.

The boys grow apart. While Jimmy becomes more aware that he has none of his parents’ aptitude for science, Crake takes off in school. Jimmy goes to a crappy art school and Crake goes to a top science university where student research is sponsored by corporations.

Later, they come back together when Crake hires Jimmy to write marketing copy for the Crake’s corporation. Jimmy becomes possessive of Oryx, a woman who previously worked as a not entirely consensual prostitute in some unknown third-world region. Now, Crake employs her at his company, and they have some nebulous relationship that drives Jimmy away from Crake.

I liked this book because Atwood made the characters seem real. She really captures the alienated teenage boy vibe in Jimmy. The highlights of the book are Jimmy’s adolescence. Besides that, Atwood creates a fascinating view of a possible future, with pigs that are genetically engineered to provide compatible organs to humans, chickens that are refactored without sense organs, brains, or anything extraneous to the purpose of getting fat enough to harvest as quickly as possible, and popular revolts battling with corporatization. Inside all that clever scenery, there’s some pretty good characterization of children abandoned by their mothers that grow into alienated adults.

The next paragraphs could be construed as spoilers, so stop reading if that sort of thing bothers you.

Besides all the good things I mentioned above, I gotta say that the ending of Oryx and Crake left me dissapointed. We never get an explanation for why Crake wiped out humanity. We spend the whole book wondering about how civilization gets destroyed, and we eventually find out how, but we never get to the more important issue of why Crake destroyed it. Crake has been a cypher throughout the plot, so it’s not out-of-character, but, like I said above, the ending is a little dissapointing.

Maybe Atwood is trying to make some point about humanity in general: people do crazy stuff and the rest of us are stuck trying to pick up the pieces and figure out why; but, to borrow a line from Marge,

“That’s a pretty lousy lesson.”