Re: database 'practice' question

Damjan

Thanks for your response. You're totally right - any ad-hoc representation is bound to be ambiguous so DDL from now on ...

Restating the situation better so we can concentrate on the real issue
...

We are only considering the first of your models i.e. A trader has many accounts, an account has exactly one trader. An account has many transactions. A transaction has exactly one account.

As you say:

CREATE TABLE trader (

    trader_id INTEGER NOT NULL PRIMARY KEY,     tradername VARCHAR(??) NOT NULL);

CREATE TABLE account (

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

CREATE TABLE transaction (

   transaction_id INTEGER NOT NULL PRIMARY KEY,    description VARCHAR(??) NOT NULL,
   profitloss MONEY NOT NULL,
   account_id INTEGER NOT NULL REFERENCES account(account_id));

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 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?

Finally, thanks for pointing out the other possibilities in your posting.

Emma Middlebrook
emma_middlebrook_at_fastmail.fm Received on Thu Nov 21 2002 - 13:24:00 CET