Re: A newbie paradox: is this a PK-FK (relationship) problem, or programming problem?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Dec 2007 16:21:19 -0400
Message-ID: <476d71c2$0$5279$9a566e8b@news.aliant.net>

tina wrote:

>>As I type this, I believe the cleanest approach is simply to have many
>>tables for different stock accounts for this individual:  one table
>>per brokerage

>
>
> that's not clean at all - it's ugly and dirty as can be. it breaks
> normalization rules, and is a nightmare to develop and maintain; every time
> you add a new stock account, you have to redesign all the objects that
> depend on the underlying tables structure - queries, forms, reports, macros,
> VBA code. i strongly recommend against it; you rarely can go wrong in
> sticking to relational design principles.
>
> basing the following remarks on the concept that a relational design will
> support multiple persons as well as multiple everything else, i'd recommend
> a minimum of six tables, as
>
> 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
> 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,

You also don't know a damned thing about his requirements. I find it absurd to offer a detailed design on the basis of complete ignorance.

but it should work as a solid core from which to build
> on. as you can see, sticking to the rules of normalization provides a clean
> setup that can allows unlimited expansion of the data without the need to
> change the objects that provide and support the user interface.
>
> hth

If only hope were enough...

> "raylopez99" <raylopez99_at_yahoo.com> wrote in message
> news:46fd0574-d042-4890-9c99-71e11f4f6c89_at_d21g2000prf.googlegroups.com...
>

>>I'm getting the hang of database architecture design I think, along
>>with easy to code, drag-and-drop Access 2003 forms programming--great
>>front end.
>>
>>But I have a question about a form involving three tables--and I'm not
>>sure if this is a programming question or a database architecture
>>question, hence the crosspost.
>>
>>I have three tables to model a stock portfolio (buying and selling by
>>a single person having numerous accounts):  Stock_Accounts (plural),
>>in a single table (red flag), which belong to a single individual,
>>then a stock table, Stocks, listing all the stocks owned by the
>>individual, then a stock transaction table, Stock_transactions,
>>listing all the buying and selling within the various accounts.  FYI
>>the table "Stock_transactions" is a subform (depends on a parent) of
>>"Stocks", while Stocks is a subform (depends on a parent) of
>>"Stock_Accounts", meaning there's a one-to-many relationship from form
>>to subform.
>>
>>Everything works fine:  everything is in first normal form with
>>primary and foreign keys, but one nagging problem:  in the rare event
>>that this person owns the same stock in two different accounts, the
>>way I set up the tables will not allow the person to enter the same
>>symbol.  Quick workaround:  require a different symbol, say "IBM2"
>>with a popup warning box to the user explaining why.  Another
>>workaround (I tried this and it works): is to eliminate the stock
>>symbol as a primary/foreign key--that's fine, and it works, but now
>>the problem is that within the same Stock account you can accidentally
>>enter the same stock symbol twice, which is a data integrity problem.
>>
>>So a third approach:  enforce relational integrity between tables for
>>stock symbol with keys involving a stock symbol, but break up the
>>different accounts into seperate tables--Account 1, Account 2, Account
>>IRA, etc.  Thus entering the same stock in Account 2 will be
>>irrelevant for this stock in Account 1, exactly as we desire.  This
>>might be the best approach.
>>
>>A fourth approach:  somehow, within the tables, enforce that the same
>>field cannot be entered twice, programmically--is there a way to do
>>that in Access?
>>
>>A fifth approach: instead of a clean "one-to-many" relationship have a
>>"many-to-many" relationship between the tables, so stock symbol
>>becomes a key but a key that is spread around (via an intermediate
>>junction table).
>>
>>As I type this, I believe the cleanest approach is simply to have many
>>tables for different stock accounts for this individual:  one table
>>per brokerage, say the person might have an IRA stock account, a
>>speculative stock account, a conservative stock account, etc, with
>>different stock brokerage account numbers, and with the accounts all
>>buying on occasion the same stock (same stock symbol), and that's
>>fine.
>>
>>Any thoughts?
>>
>>RL

>
>
>
Received on Sat Dec 22 2007 - 14:21:19 CST