Re: database 'practice' question

From: Larry Coon <larry_at_assist.org>
Date: Thu, 21 Nov 2002 16:15:10 -0800
Message-ID: <3DDD770E.76C5_at_assist.org>


emma middlebrook wrote:

> The pertinent question in my ramble is the following which I can
> restate a little better now: what are the pros and cons of having the
> foreign keys in the tables themselves rather than representing this in
> a separate table *given this particular model*?

Either way works, provided you handle the cardinality correctly (and as Damjan pointed out, you didn't do it correctly in one of your examples, so one factor might be which design is more resistant to error).

Second, like I said, you need to perform another join every time you access the data.

Third, you're putting more stuff in your database -- one more table, and one more index, plus the account ID now appears in two tables. Not that this is any crime, but given two competing designs, I naturally prefer the simpler one.

Fourth, this is simply the common way to do it. If I was reading your schema, I might mistakenly assume, because you used a third table, that the cardinality was many:many, unless I really looked closely.

Again, keep it simple, and don't go against convention without a good reason. I haven't heard a good reason to go against convention here.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Fri Nov 22 2002 - 01:15:10 CET

Original text of this message