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

From: David Cressey <cressey73_at_verizon.net>
Date: Sun, 23 Dec 2007 11:31:32 GMT
Message-ID: <oGrbj.4456$we6.2807_at_trndny09>


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

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/ Received on Sun Dec 23 2007 - 12:31:32 CET

Original text of this message