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

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

"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 - 20:57:23 CET