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

From: raylopez99 <raylopez99_at_yahoo.com>
Date: Sat, 29 Dec 2007 06:51:29 -0800 (PST)
Message-ID: <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.

I have various ideas on how to fix this programmically (such as a simple lookup table so when the combination of particular Bank Account and CD ID are found, a warning box will tell the user that this record already exists).

However, I can't, under the below architecture, seem to do this via "relationships" in Access. I tried various combinations, but each time something went wrong. My most promising (I thought) attempt was to replace in the critical linking table "AccountStocks" below (please substitute "CD" for "stocks" below, because the architecture for CD and Stocks is exactly the same) with a new primary COMPOUND key comprising: StockSymbol (i.e. CD Symbol) and AcctID. However, this compound key creation did not give me what I wanted, in that the form I used did not allow me to create a new record twice having a different StockSymbol/CD ID but the same AcctID. I can fix this by playing around with the form, by redesigning it so that CD is no longer in a relationship with the AccountStocks/AccountCD table, but rather is simply another field, or part of a combo box or list box, with a message box warning as I discussed above, and perhaps that's the solution (a programmical solution in my mind), but I wonder if there's something else I'm missing from an architecture point of view. Can I do something like what I have in mind by a judicious selection of keys (relationships)?

FYI here is the SQL query linking the "Transactions" table with the "AccountCD" table (the junction table) (maybe the SQL query is wrong? But it doesn't seem to impact what happens further upstream, and it looks pretty generic to me--just selecting all the fields in both tables and equi-joining them with a common Account CDID from the parent (AccountCD) table).

SELECT AccountCDs.AccountCDID, AccountCDs.CDSymbol, AccountCDs.AcctID,

CDTransactions.Account_CDID, CDTransactions.dateDeposit,
CDTransactions.Principal, CDTransactions.[interest rate],
CDTransactions.TransactionsPK

FROM AccountCDs INNER JOIN CDTransactions ON AccountCDs.AccountCDID = CDTransactions.Account_CDID;

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,
Received on Sat Dec 29 2007 - 15:51:29 CET