Re: database 'practice' question

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."

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));

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 Thu Nov 21 2002 - 13:44:51 CET