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

"raylopez99" <raylopez99@yahoo.com> wrote in message 
news:46fd0574-d042-4890-9c99-71e11f4f6c89@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


Please do yourself a favour and study a decent book on design theory. People 
reading what you wrote probably have little chance of correctly 
understanding what you mean and even less chance of guessing the right 
solution. If guesswork is more valuable to you than your own ability to 
solve the problem then you certainly need more help than you can get in 
these newsgroups.

-- 
David Portas

> 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@yahoo.com> wrote in message
news:46fd0574-d042-4890-9c99-71e11f4f6c89@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

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@yahoo.com> wrote in message
> news:46fd0574-d042-4890-9c99-71e11f4f6c89@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
> 
> 
> 

I don't see you making any suggestions. Surely you don't think that one 
table per brokerage could ever be an appropriate answer!

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Bob Badour" <bbadour@pei.sympatico.ca> wrote in message 
news:476d71c2$0$5279$9a566e8b@news.aliant.net...
>
> 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.
>
>>>
>>>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
>>
>> 

i had to chuckle at this one, Doug. when i first opened your post, i thought
you were responding to my post, and i couldn't figure out what you were
talking about! then i scrolled down, and found that you were replying to
another post that i had not seen, having blocked that particular user some
time ago.  ;)
ps. i haven't been in the ngs for quite awhile; nice to "see" you, and i
hope you have a safe and happy holiday!  :)


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23DrWlyNRIHA.3400@TK2MSFTNGP03.phx.gbl...
> I don't see you making any suggestions. Surely you don't think that one
> table per brokerage could ever be an appropriate answer!
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Bob Badour" <bbadour@pei.sympatico.ca> wrote in message
> news:476d71c2$0$5279$9a566e8b@news.aliant.net...
> >
> > 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.
> >
> >>>
> >>>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
> >>
> >>
>

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@yahoo.com> wrote in message
> > news:46fd0574-d042-4890-9c99-71e11f4f6c89@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
> > 
> > 
> > 
> 

Douglas J. Steele wrote:

> I don't see you making any suggestions. Surely you don't think that one 
> table per brokerage could ever be an appropriate answer!

Douglas,

David Portas already covered everything quite well, and I didn't have 
much to add to his post.

Ray,

I suggest you learn to identify those people who Fabian Pascal has 
dubbed the Vociferous Ignorami. Some of them have obligingly 
self-identified by declaring themselves "Most Vociferous People" (MVP). 
While not all of the self-aggrandizing ignorants so self-identify, the 
designation is a reliable indicator for those who do.

If you think about things for even a millisecond, you will recognize the 
importance and substance of the absurdity of lengthy and detailed design 
on the basis of complete ignorance. I think you will find the replies to 
the observation lack substance entirely. I will leave it for you to judge.

As for your original problem, your declared constraints do not match 
your conceptual model of how the system should behave. That is a clear 
sign that one or both are wrong.

I reiterate David's suggestion to learn the theory so you can understand 
and evaluate these things for yourself. I also draw your attention to 
David's predictions regarding guesswork.

On Sat, 22 Dec 2007 21:38:20 GMT, "tina" <nospam@address.com> wrote:

>ps. i haven't been in the ngs for quite awhile; nice to "see" you, and i
>hope you have a safe and happy holiday!  :)

Welcome back, Tina! Good to see you, and a merry Christmas to you!

Boy... crossposting outside the microsoft tree can bring in some strange
stuff, eh?

             John W. Vinson [MVP]

"raylopez99" <raylopez99@yahoo.com> wrote in message
news:46fd0574-d042-4890-9c99-71e11f4f6c89@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.
>

You're the same person who was building a home bookkeeping app in Access,
right?
That was a hobby, right?  Is the app you are describing below another hobby
app?
It's a little more ambitious than home books.

> 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.
>
This is the solution, but you haven't described the problem.  I don't
understand your data and your intended use of it well enough to offer an
opinion about whether your design is optimal, nearly optimal, or severely
suboptimal.  That's the feedback I think you ask for towards the end of this
post.

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

Requiring a different symbol is nearly always an invitation to dysfunction.
Remember what your grandmother said:  "Oh, what a tangled web we weave, when
first we practice to deceive."  If you store some real data and some
invented data in the same column, you'll regret it.

If the relationship between stocks and accounts is many to many,  model it
that way,  and implement it the right way.




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

It's the dirtiest approach.

> Any thoughts?
>

You really do need to learn a little more about database design from some
formal source.  You've gotten off to a satisfying start with trial and
error,  mere intuition, and feedback from a newsgroup.  But you are about to
reach areas where your intuition will betray you,  the newsgroup can't
really help you, except to point to to better sources of learning,  and
trial and error is just going to be too expensive,  even for a hobbyist with
plenty of time.

Some people have given you the names of some books.  You'll learn more out
of those books than from any website.  But if you want to get started with
some websites,  here are a couple:

http://www.utexas.edu/its-archive/windows/database/datamodeling/dm/overview.html
http://www.databaseanswers.org/

On Dec 23, 6:31=A0am, "David Cressey" <cresse...@verizon.net> wrote:

Thanks David Cressey.

I do have some books, and am working through the David Louison book,
and at some point might buy more books, but it seems to me that there
is no formal math you can learn to make a database normalized; indeed
"trial and error" and intuition is what works.

Obviously you, a 20+ year veteran, and some of the other posters here
have a lot more trial and error experience than I do.

BTW I did like the solution by Tina--it seems to do the trick in
segregating symbol from brokerage account, which was I think my
problem in the original design.

Also my proposed clean (dirty) solution in retrospect is not that
scalable...

RL

"raylopez99" <raylopez99@yahoo.com> wrote in message
news:e4020d50-9327-4597-8be6-414c771b829e@f3g2000hsg.googlegroups.com...
On Dec 23, 6:31 am, "David Cressey" <cresse...@verizon.net> wrote:

> Thanks David Cressey.

>I do have some books, and am working through the David Louison book,
>and at some point might buy more books, but it seems to me that there
>is no formal math you can learn to make a database normalized; indeed
> "trial and error" and intuition is what works.

Well, I tried... but that may have been my error.


>Obviously you, a 20+ year veteran, and some of the other posters here
>have a lot more trial and error experience than I do.

I had a lot of learning modes other than trial and error available to me 20+
years ago.  The best was mentoring from people who knew more than I did.


>BTW I did like the solution by Tina--it seems to do the trick in
>segregating symbol from brokerage account, which was I think my
>problem in the original design.


>Also my proposed clean (dirty) solution in retrospect is not that
>scalable...

Good luck.

thank you, John, and Merry Christmas to you too!  :)


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:kh9rm3p2a9lo8pvu42i3m47biba7ombqdi@4ax.com...
> On Sat, 22 Dec 2007 21:38:20 GMT, "tina" <nospam@address.com> wrote:
>
> >ps. i haven't been in the ngs for quite awhile; nice to "see" you, and i
> >hope you have a safe and happy holiday!  :)
>
> Welcome back, Tina! Good to see you, and a merry Christmas to you!
>
> Boy... crossposting outside the microsoft tree can bring in some strange
> stuff, eh?
>
>              John W. Vinson [MVP]

you're welcome; as i said, it should give you a solid core guideline. just
stick to the normalization rules and you'll be fine.  :)


"raylopez99" <raylopez99@yahoo.com> wrote in message
news:43c5d36b-e576-4ad0-b207-d4e43ce1d7dc@i3g2000hsf.googlegroups.com...
On Dec 22, 4:38 pm, "tina" <nos...@address.com> wrote:

Thanks Tina!  I like your solution, it seems to make sense and even be
in Third Normal Form or somesuch...very nice!

I will model it and if I have any problems will report back.

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

well, i don't think i understand what you mean by "formal math", ray, but
you can indeed learn to understand and apply the rules of normalization from
a book - that's exactly how i learned. experience certainly makes it easier
to do as time goes on (though i still get stumped at times, especially when
the relationships aren't the standard linear ones i'm used to working with).
but unless you've already been trained to "think relationally", intuition is
not going to get you there - at least it didn't do it for me. many times in
these newsgroups, i've recommended Michael Hernandez' Database Design for
Mere Mortals, and i stand by that recommendation. (that's the book i learned
from, used as the textbook in a night school class i took on relational
design.) i believe it will be well worth your time and money to buy a copy
and read it cover to cover, practicing the concepts as you go. good luck
with your project!  :)


"raylopez99" <raylopez99@yahoo.com> wrote in message
news:e4020d50-9327-4597-8be6-414c771b829e@f3g2000hsg.googlegroups.com...
On Dec 23, 6:31 am, "David Cressey" <cresse...@verizon.net> wrote:

Thanks David Cressey.

I do have some books, and am working through the David Louison book,
and at some point might buy more books, but it seems to me that there
is no formal math you can learn to make a database normalized; indeed
"trial and error" and intuition is what works.

Obviously you, a 20+ year veteran, and some of the other posters here
have a lot more trial and error experience than I do.

BTW I did like the solution by Tina--it seems to do the trick in
segregating symbol from brokerage account, which was I think my
problem in the original design.

Also my proposed clean (dirty) solution in retrospect is not that
scalable...

RL

"tina" <nospam@address.com> wrote:

>well, i don't think i understand what you mean by "formal math", ray, but

     Formal means you can use it to construct proofs.

     For example, it is "obvious" that the product of two odd integers
is itself odd, but it can be proven with a formal system.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
     I have preferences.
     You have biases.
     He/She has prejudices.

On Dec 23, 1:25=A0pm, "tina" <nos...@address.com> wrote:
>. many times in
> these newsgroups, i've recommended Michael Hernandez' Database Design for
> Mere Mortals, and i stand by that recommendation. (that's the book i learn=
ed
> from, used as the textbook in a night school class i took on relational
> design.) i believe it will be well worth your time and money to buy a copy=

> and read it cover to cover, practicing the concepts as you go. good luck
> with your project! =A0:)
>

Hello Tina--Everything worked fine, exactly as you planned it, thanks
again, it's perfect with one small caveat (I will repost this question
in microsoft.public.=ADaccess.formscoding in case you or anybody else
misses it here):  this is an Access database programming question (I
think), and it's very basic and simple:  in the final two tables,
"tblAccountStocks" and "tblTransactions", linked by AcctStockID, I
want to add a field (the stock symbol) from the parent table
tblAccountStocks form, so that it appears (i.e., is read only) in the
child subform (which has data control record source tblTransactions of
course).  Mainly so the user of the form has a visual clue, not to
populate any table (i.e., the field is read only).  But in the drop
down List box data source: Properties | Data | Control Source these
parent fields don't show up (they never do--that's the heart of the
problem, and I'm wondering if there's something I'm missing).  Only
the migrated primary key (i.e. the foreign key) which in your example
was "AcctStockID (fk)" shows up, as well as the other fields of the
subform table of course.  I want to add to these fields with a stock
symbol field from the parent table, since it's less confusing to the
user using the subform.  Here's what I did, and it works, but I'm
wondering if there's a more elegant solution:  I simply added another
primary key, "StockSymbol (pk)" in the parent form (tblAccountStocks),
and so now there are two primary keys (a compound key), then I
migrated this newly added key (i.e. made it a foreign key) for the
child subform table "tblTransactions".  Thus the form and subform are
now linked by two keys rather than one:  AccountStockID;StockSymbol
when you click on the subform properties under the heading "Link
Childfields", "Link Master fields".  This workaround worked
swimmingly, but it seems this workaround violates database design a
bit, and I'm wondering if I can somehow directly show a field from the
parent form in the child subform without going through this tedious
workaround (preferably without touching any Visual Basic code or
[procedures], but I can deal with VB if I have to)

Thanks!

RL

"tina" <nospam@address.com> wrote:

>used as the textbook in a night school class i took on relational
>design.

<shrug> I have no training in relational design or computers or
whatever.  Well not quite I do have 3 credit hours as a teenager.

Tony
-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Bob Badour <bbadour@pei.sympatico.ca> wrote:

(microsoft.public.access.formscoding added back in.)

>I suggest you learn to identify those people who Fabian Pascal has 
>dubbed the Vociferous Ignorami. Some of them have obligingly 
>self-identified by declaring themselves "Most Vociferous People" (MVP). 
>While not all of the self-aggrandizing ignorants so self-identify, the 
>designation is a reliable indicator for those who do.

BWAHAHAHAHAHA

Thanks for the guffaw.

Tony
-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

"tina" <nospam@address.com> wrote:

>i had to chuckle at this one, Doug. when i first opened your post, i thought
>you were responding to my post, and i couldn't figure out what you were
>talking about! then i scrolled down, and found that you were replying to
>another post that i had not seen, having blocked that particular user some
>time ago.  ;)

I was also wondering as to why there were rather rude comments and
then I see the comp.databases.theory in the newsgroup cross posting
list.

>ps. i haven't been in the ngs for quite awhile; nice to "see" you, and i
>hope you have a safe and happy holiday!  :)

Nice to see you back as well.

Tony
-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Tony Toews [MVP] wrote:

> Bob Badour <bbadour@pei.sympatico.ca> wrote:
> 
> (microsoft.public.access.formscoding added back in.)
> 
>>I suggest you learn to identify those people who Fabian Pascal has 
>>dubbed the Vociferous Ignorami. Some of them have obligingly 
>>self-identified by declaring themselves "Most Vociferous People" (MVP). 
>>While not all of the self-aggrandizing ignorants so self-identify, the 
>>designation is a reliable indicator for those who do.
> 
> BWAHAHAHAHAHA
> 
> Thanks for the guffaw.
> 
> Tony

You're welcome.

(Simple minds are so easily amused.)

Tony Toews [MVP] wrote:

> "tina" <nospam@address.com> wrote:
> 
> 
>>used as the textbook in a night school class i took on relational
>>design.
> 
> <shrug> I have no training in relational design or computers or
> whatever.  Well not quite I do have 3 credit hours as a teenager.
> 
> Tony

I am sure you are dutifully proud of your ignorance. That's one of the 
hallmarks of the vociferous ignorami.

Bob Badour <bbadour@pei.sympatico.ca> wrote:

(microsoft.public.access.formscoding added back to the newsgroup
listing.)

>> <shrug> I have no training in relational design or computers or
>> whatever.  Well not quite I do have 3 credit hours as a teenager.
>
>I am sure you are dutifully proud of your ignorance. That's one of the 
>hallmarks of the vociferous ignorami.

Am I supposed to be insulted by your comment?

Tony
-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

"Tony Toews [MVP]" <ttoews@telusplanet.net> wrote in message
news:fh0um3hk5ofejr289n1kvhutjpdu6n0a09@4ax.com...
> "tina" <nospam@address.com> wrote:
>
> >i had to chuckle at this one, Doug. when i first opened your post, i
thought
> >you were responding to my post, and i couldn't figure out what you were
> >talking about! then i scrolled down, and found that you were replying to
> >another post that i had not seen, having blocked that particular user
some
> >time ago.  ;)
>
> I was also wondering as to why there were rather rude comments and
> then I see the comp.databases.theory in the newsgroup cross posting
> list.

hmm, well, i never pay attention to what groups the message is posted to;
i'm concentrating on trying to help the OP, not worrying about who else
might be reading. though i'm always grateful to have my mistakes corrected
(especially when it's done courteously) because then i learn something, as
well as the OP.

>
> >ps. i haven't been in the ngs for quite awhile; nice to "see" you, and i
> >hope you have a safe and happy holiday!  :)
>
> Nice to see you back as well.

thanks, Tony, happy holidays!  :)

>
> Tony
> -- 
> Tony Toews, Microsoft Access MVP
>    Please respond only in the newsgroups so that others can
> read the entire thread of messages.
>    Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm
>    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

but i think you're considerably smarter than me, Tony <bows, smiling> i
definitely needed the training i got from the book and the class!  :)


"Tony Toews [MVP]" <ttoews@telusplanet.net> wrote in message
news:4l0um3t0i0djqtuiopk8ei2bv385al7hpm@4ax.com...
> "tina" <nospam@address.com> wrote:
>
> >used as the textbook in a night school class i took on relational
> >design.
>
> <shrug> I have no training in relational design or computers or
> whatever.  Well not quite I do have 3 credit hours as a teenager.
>
> Tony
> -- 
> Tony Toews, Microsoft Access MVP
>    Please respond only in the newsgroups so that others can
> read the entire thread of messages.
>    Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm
>    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Tony Toews [MVP] wrote:
> Bob Badour <bbadour@pei.sympatico.ca> wrote:
> 
> (microsoft.public.access.formscoding added back to the newsgroup
> listing.)
> 
>>> <shrug> I have no training in relational design or computers or
>>> whatever.  Well not quite I do have 3 credit hours as a teenager.

wow, wish I coulda done that... i had 8 years as a teenager... seemed 
longer.

>> I am sure you are dutifully proud of your ignorance. That's one of the 
>> hallmarks of the vociferous ignorami.
> 
> Am I supposed to be insulted by your comment?

Find an online dictionary and *know* if your supposed to be insulted or 
not.  BTW, your experience and what you're trying to do make you a 
"hacker" not a newbie.  Newbies are generally defined as people *in* a 
field who are just starting, not people who aren't in a field and don't 
plan on being so.

The subject is "relational database design" and it really is a 
fascinating field and a useful discipline. There are some well written 
books available.

I know a few MVP's; y'all generally work pretty hard trying to make up 
for what M$ and their customers can't be arsed to do, so...

Vague oracular hint: a "Stocks" record with Abbreviation as a PK and 
possibly <relevant cough> another thing or two might go a long way 
towards solving your problem, as well as noting that a screen-display is 
a data-structure.


rpl

rpl wrote:

> Tony Toews [MVP] wrote:
> 
>> Bob Badour <bbadour@pei.sympatico.ca> wrote:
>>
>> (microsoft.public.access.formscoding added back to the newsgroup
>> listing.)
>>
>>>> <shrug> I have no training in relational design or computers or
>>>> whatever.  Well not quite I do have 3 credit hours as a teenager.
> 
> wow, wish I coulda done that... i had 8 years as a teenager... seemed 
> longer.
> 
>>> I am sure you are dutifully proud of your ignorance. That's one of 
>>> the hallmarks of the vociferous ignorami.
>>
>> Am I supposed to be insulted by your comment?

I added Tony to my twit filter so I didn't see this directly. To answer 
his question: I don't give a rat's ass about him or any other 
self-aggrandizing ignorant. I posted the comment for others so they can 
be informed.

Bob Badour <bbadour@pei.sympatico.ca> wrote:

>>> Am I supposed to be insulted by your comment?
>
>I added Tony to my twit filter so I didn't see this directly. To answer 
>his question: I don't give a rat's ass about him or any other 
>self-aggrandizing ignorant. I posted the comment for others so they can 
>be informed.

BWAHAHAHAHA

Thanks.  I'll miss you too.

Tony
-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

raylopez99 wrote:

> On Dec 25, 12:50 pm, Bob Badour <bbad...@pei.sympatico.ca> wrote:
> 
> 
>>I added Tony to my twit filter so I didn't see this directly. To answer
>>his question: I don't give a rat's ass about him or any other
>>self-aggrandizing ignorant. I posted the comment for others so they can
>>be informed.-
> 
> Hey Bob, isn't 'ignorant' an adjective?  So you should write
> "ignoramus"!  U ignorant or what?  LOL.

Not when I used it.

rpl <plinnane3@yahoo.com.invalid> wrote:

>> Am I supposed to be insulted by your comment?
>
>Find an online dictionary and *know* if your supposed to be insulted or 
>not.  

Not worth the time.

>BTW, your experience and what you're trying to do make you a 
>"hacker" not a newbie.  Newbies are generally defined as people *in* a 
>field who are just starting, not people who aren't in a field and don't 
>plan on being so.

Umm, but I'm not the original poster.  I consider myself to be
somewhere in the systems analyst/programmer/developer/business analyst
continuum.

I may not have the theory but I do design and create some fairly large
systems in Access.  At least very large in terms of Access.  Not, of
course, as large as some enterprise systems with hundreds or thousands
of users.

http://blog.datamanagementsolutions.biz/2006/05/real-world-access-6.html

Tony


-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Bob Badour wrote:
> rpl wrote:
> 
>> Tony Toews [MVP] wrote:
>>
>>> Bob Badour <bbadour@pei.sympatico.ca> wrote:
>>>
>>> (microsoft.public.access.formscoding added back to the newsgroup
>>> listing.)
>>>
>>>>> <shrug> I have no training in relational design or computers or
>>>>> whatever.  Well not quite I do have 3 credit hours as a teenager.
>>
>> wow, wish I coulda done that... i had 8 years as a teenager... seemed 
>> longer.
>>
>>>> I am sure you are dutifully proud of your ignorance. That's one of 
>>>> the hallmarks of the vociferous ignorami.
>>>
>>> Am I supposed to be insulted by your comment?
> 
> I added Tony to my twit filter so I didn't see this directly. To answer 
> his question: I don't give a rat's ass about him or any other 
> self-aggrandizing ignorant. I posted the comment for others so they can 
> be informed.

Well, I originally thought Tony was Ray; I had cancelled the post you 
copied it from a couple minutes after I posted it... but glad to be of 
service :D


rpl