Re: A philosophical newbie issue: catch redundant errors via relationships or programmically?

"raylopez99" <raylopez99_at_yahoo.com> wrote in message news:dabe9ee9-bae3-4ce2-9b3f-9f76e963f596_at_p69g2000hsa.googlegroups.com...
> I'm a newbie to dB programming. I set up a set of tables (see below
> after the RL signoff) to capture the relationship between a group of
> people buying and selling stocks, and it worked well (using Visual
> Basic Access for the front end). Now I want to extend this
> architecture of tables to a group of people buying and redeeming CDs
> (Certificates of Deposit) at banks.
>
> Everything worked fine for this CD extension, I set in up in no time,
> but I noticed that when a user enters data, sometimes the same
> combination of Bank Account number and CD identifier (CD ID) will be
> entered by mistake, which under the below architecture will create a
> new record. I don't want this.
>
[snip]

> RL
>
>
> tblPersons
> > PersonID (pk)
> > FirstName
> > MiddleInitial
> > LastName
> > <other fields that describe the person only.>
>
> > tblStocks
> > StockSymbol (pk)
> > StockName
> > <other fields that identify the stock only.>
>
> > tblBrokerages
> > BrokID (pk)
> > BrokName
>
> > tblAccounts
> > AcctID (pk)
> > PersonID (fk)
> > BrokID (fk)
> > <other fields that describe a specific account for a specific person.>
>
> > tblAccountStocks <----------This table is key, a junction/linking table
> > AcctStockID (pk)
> > AcctID (fk)
> > StockSymbol (fk)
>
> > tblTransactions
> > TransID (pk)
> > AcctStockID (fk)
> > <other fields that describe a specific transaction of a specific stock
in a
> > specific account.>
>
> > the relational structure is
> > tblPersons.PersonID 1:n tblAccounts.PersonID
> > tblBrokerages.BrokID 1:n tblAccounts.BrokID
> > tblAccounts.AcctID 1:n tblAccountStocks.AcctID
> > tblStocks.StockSymbol 1:n tblAccountStocks.StockSymbol
> > tblAccountStocks.AcctStockID 1:n tblTransactions.AcctStockID
>
> > tblAccounts is a junction (linking) table between tblPersons and
> > tblBrokerages.
> > tblAccountStocks is a junction (linking) table between tblAccounts and
> > tblStocks.
> > and tblTransactions is a simple child table of tblAccountStocks.
> > so you can trace each transaction record back to a specific stock in a
> > specific account belonging to a specific person.
>
> > i don't know a thing about stock markets and trading, so i imagine this
is a
> > simplified structure,

The design of table tblAccountStocks is unfortunate.

If you eliminate the column AcctStockId, and make the pk of that table be two columns, AcctID and Stocksymbol, then the same combination of AcctID and Stocksymbol cannot be entered more than once.

References to AcctStockID in other tables will have to be replaced with references to AcctID and Stocksymbol.

Is this the table where you are having trouble forbidding duplicates? If not, which table is it? Received on Mon Dec 31 2007 - 11:55:38 CET