Re: database 'practice' question

> 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*?
>
> CREATE TABLE account (
> account_id INTEGER NOT NULL PRIMARY KEY,
> balance MONEY NOT NULL,
> trader_id INTEGER NOT NULL REFERENCES trader(trader_id));
>
> ... compare with ...
>
> CREATE TABLE account(
> account_id INTEGER NOT NULL PRIMARY KEY,
> balance MONEY NOT NULL);
>
> CREATE TABLE traderaccount (
> trader_id INTEGER NOT NULL REFERENCES trader(trader_id),
> account_id INTEGER NOT NULL REFERENCES account(account_id),
> PRIMARY KEY(trader_id, account_id));
>
> Are there any 'normal form' issues here - a set of tables with no
> foreign keys feels better but am not sure why.
>
> I understand that the pair of tables (account and traderaccount) allow
> you a many-many relationship but we don't have to worry about this in
> my case.
>
> I am wondering about how 'clean'/'pure' the first definition for
> account is because it includes a foreign key. With the second version
> we have 'encapsulated' the information better because we have

And why is that important? And the information about account's owner is VERY related to account itself (see below).

> information just relating to the account? The relation is held
> somewhere else (in traderaccount table). I understand that the first
> model means less joins. I'm thinking about this from a purity of
> design issue in this particular model. Do you see what I'm getting at?

The two models you are talking about are different in a lot more than 'purity'. They simply represent two different systems (business rules are different). In the 'second' model (the one with account & traderaccount) you can have an instance of account without an owner (account can have ZERO or ONE owner(s - whatever), which is terribly bad idea, I think). The 'first' model won't let you create an account without specifying it's owner (account has exactly ONE owner).

Regarding normalisation issues, both models are equal (all tables are in BCNF). Mr Larry Coon gave you some (quite pracitcal) additional reasons in favour of 'model one', so...

Regards,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Yugoslavia

http://galeb.etf.bg.ac.yu/~damjan/ Received on Fri Nov 22 2002 - 00:26:35 CET