Re: A newbie paradox: is this a PK-FK (relationship) problem, or
- From: boblarson <boblarson@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 22 Dec 2007 13:43:01 -0800
I have to agree with Doug about that response. It is not in the least
helpful and definitely rude. Tina was offering up some suggestions on how it
might be accomplished, and yes she doesn't know all of the requirements (the
person didn't go into all of that), but there ARE some SIMPLE design concepts
that this SAMPLE can get across. The least of which is NORMALIZE, NORMALIZE,
NORMALIZE!!!!
The Original Poster's post definitely showed a lack of knowledge in that
area and so some example of such methods was called for, without even knowing
all about his business. So, take a chill pill and maybe tone the use of the
word DAMN down a bit.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________
"Bob Badour" wrote:
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@xxxxxxxxx> wrote in message
news:46fd0574-d042-4890-9c99-71e11f4f6c89@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
- Prev by Date: Re: subform not found on mainform
- Next by Date: Re: subform not found on mainform
- Previous by thread: Sorting a field with calculations in it in a report.
- Next by thread: Re: custom control to display images in Continuous Forms
- Index(es):
Relevant Pages
|
