As you suggested, you index both MonkID and HobbyID because
you're planning to search for all monks with a particular
hobby and all the hobbies of a specific monk.
But that doesn't mean you won't want a single primary
key rather than a composite one. The db theory book
I've got here says "When possible we should try
to avoid composite primary keys for entities..."
although it isn't kind enough to offer a compelling
reason, or really any reason at all. If you ever need
to refer any of the tuples in the third table within
another table, you'll probably want a single key.
But I seem to recall that you can use the internal
tuple identification (in Postgres, at least, where I
believe it is the oid) rather than a sequence for such
cases. I can't access the postgres site currently,
so I'm unable to verify that, and I don't know if that's
possible or recommended with other databases.
| [reply] |
I get nervous doing that - most of my database design is from
reading one book many, many moons ago and then a lot of
work.
I think auto-increment keys like that are cleaner. By using
a database, you have kind of already said you are not *that*
concerned with size. An extra INT between friends shouldn't
matter. And composite keys, especially when referring to
foreign keys, just sound very dangerous to me.
But I have no theory to back any of this up - it is your
database to design as you wish.
Mik | [reply] |