Re: database 'practice' question

From: Damjan S. Vujnovic <damjan_at_NOSPAMgaleb.etf.bg.ac.yu>
Date: Wed, 20 Nov 2002 17:38:58 -0800
Message-ID: <argdpb$fti$1_at_news.etf.bg.ac.yu>


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

First of all, when describing your model use DDL, it's a standard. I'm not sure I understand your AND/OR scheme.

> We have some people (traders). A person may have many accounts

But whether any account can have more than one owner?

> (accounts). Each account has many transactions (trades) associated
> with it.

Whether any transaction can be associated with more than one account?

> 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. I can't work out really why
> you'd have one over the other and for what reasons? I mean, you could
> argue that the trader who owns the account is nothing to do with the
> account itself and so should be in a different 'look-up' table. How
> would you think about it?

Depends whether account is owned by one and exactly one trader or not. If every account has exactly one owner then the model should be somewhat like:

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

If this is not the case (account has more than one owner), then the following will do:

CREATE TABLE trader (

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

CREATE TABLE account (

    account_id INTEGER NOT NULL PRIMARY KEY,     balance MONEY NOT NULL);

CREATE TABLE has (

    trader_id INTEGER NOT NULL REFERENCES trader(trader_id),     account_id INTEGER NOT NULL REFERENCES account(account_id),     PRIMARY KEY(trader_id, account_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));

As you can see, in this model you can represent the fact that two traders share one account. In the previous one you can't. I think your problem is that you haven't completely and clearly formulated your task. There are many possibilities depending on what do you want to support (and also what do you want to forbid) with your model. Of course, you can always use IDENTITY constraint and introduce tables such as HAS, but such an approach has deficiencies.

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 Thu Nov 21 2002 - 02:38:58 CET

Original text of this message