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

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 - 19:44:36 CET