Re: database 'practice' question

emma middlebrook wrote:

> This was the most relevant newsgroup for my query despite its title
> and despite my question! I hope someone can help ...
>
> This is the setup.
>
> We have some people (traders). A person may have many accounts
> (accounts). Each account has many transactions (trades) associated
> with it.
>
> Trying to think which is 'better' and why: to include TraderID as a
> foreign key of Accounts or to have a *separate* table which contain
> the 'links' between traders and accounts.

It really depends on the cardinality between traders and accounts. If it's many:many, then you really need to use a third table. 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.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Wed Nov 20 2002 - 19:30:50 CET