Re: database 'practice' question

> Larry

It's me, Damjan, not Larry ;-)

>
> As Damjan pointed out, my post was ambiguous and so restated it in his
> reply. You pretty much got to the essence though!
>
> You were spot on with your guess that I mentioned only the relations
> in existence (the relations are in fact: one trader can have many
> accounts can have many transactions but a transaction has exactly one
> account has exactly one trader. transaction has exactly one trader
> too.)
>
> Here's something from your post:
>
> "If it's one:many (one trader can own many accounts, but each account
> is owned by one and only one trader), then it's common to use a
> foreign key to express the relationship. You COULD still use a third
> table, but there's really no good reason to do that, and I can see a
> couple of problems off the top of my head:
>
> 1) Accessing the data always requires an extra join.
>
> 2) It's harder to enforce the cardinality. If an account is owned by
> one trader, then you'd need a unique index on the account column in
> the junction table to enforce the fact that two rows can't exist in
> the junction table for the same account."

To Larry: Why not setting account_id as primary key (not the whole pair account_id, trader_id)??? It still enforces cardinality...

>
> The pertinent question in my ramble is the following which I can
> restate a little better: what are the pros and cons of having the
> foreign keys in the tables themselves rather than in a separate table?
> "could still use a third table" - any theoretical/purity reasons?
>
> OK - so I see the extra join problem. Am a little unsure about your
> point 2 though - are you just saying that you need to do this i.e.
> just remember to define a primary key in the junction table - this
> doesn't seem to be much of an extra burden - depending on whether
> there are any benefits :-)) :
>
> CREATE TABLE traderaccounts (
> trader_id INTEGER NOT NULL REFERENCES trader(trader_id),
> account_id INTEGER NOT NULL REFERENCES account(account_id),
> PRIMARY KEY(trader_id, account_id));

Wrong. As Larry pointed out you didn't enforce the cardinality. Now an account can have many, many, many owners. Hint:

CREATE TABLE traderaccounts (

    trader_id INTEGER NOT NULL REFERENCES trader(trader_id),     account_id INTEGER NOT NULL REFERENCES account(account_id),     PRIMARY KEY(account_id));

Ok?

> I'm thinking about whether it's 'nicer'/'cleaner'/'purer'/of-a-'higher
> normal form' to make sure that you separate out all foreign keys and
> put them in separate 'junction tables' as you mention. It seems better
> 'encapsulation' to have these 'junction tables' always and avoid
> foreign keys?
>
> I get the practical issues you mention, just wondering about this from
> a theoretical point of view now (or maybe not, if I have missed
> something about the practical bits) - ah, so I am posting to the right
> newsgroup after all!!!
>
> Thanks for your help so far!
>
> Emma Middlebrook
> emma_middlebrook_at_fastmail.fm
Received on Fri Nov 22 2002 - 00:43:01 CET