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_at_news.aliant.net>
>
>
> 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,
>
>
>
Received on Sat Dec 22 2007 - 21:21:19 CET
Date: Sat, 22 Dec 2007 16:21:19 -0400
Message-ID: <476d71c2$0$5279$9a566e8b_at_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
If only hope were enough...
> 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
> 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 - 21:21:19 CET