Re: A philosophical newbie issue: catch redundant errors via relationships or programmically?

raylopez99 wrote:

> OK wise guy <g>, I kindly beseech thee to give me a simple schema that
> will do the following: "to declare a uniqueness constraint as a
> foreign key reference" that allows me to do the following:

Where did you get the quoted part? It is unclear.

> Table Certificate of Deposit provides a series of fixed income
> products (CDs, Bonds, notes, etc, that are portable, in that you can
> deposit them anywhere)--hereinafter "CD". Primary key right now is
> "CD SYmbol" (alphanumeric designator of the product:
> XYZABC_Bond_matures_2008)
>
> Table Bank has a primary key comprising: An account number that is
> unique to every person--hereinafter "Bank"

Why not call it 'Persons' if - as I read from this - it is supposed to hold data about persons?

> Now I want to create a junction table called "AccountCD" that combines
> the primary key of both the two tables above.

Do I get you right? Two foreign keys in the junction: AccountCD.CD_symbol referencing 'Certificate of Deposit' and AccountCD.Bank_account referencing 'Bank', and 'AccountCD' should have the combination of AccountCD.CD_symbol and AccountCD.Bank_account as primary key.

> I do this, but unfortunately in Access I cannot enter a combination of
> "CD" and "Bank" that is unique.

If I may read this as: It won't allow me to define a primary key as a combination of columns: How sad (NOT kidding). I suspect, that if you use a DBMS as a back-end instead of the built-in, you can.

> THat is, either the table allows me
> to enter the same CD with the same Bank twice***, or, if I play around
> with the keys, it forbids me from entering either (1) the same bank
> twice or (2) the same CD twice. BTW, this last restriction (1) I can
> live with, but I don't want it--that is, I can set up a AccountCD
> table that has only one record per bank, and different CDs in the
> record, but what I want (because I'm too lazy to change my schema now)
> is a series of unique combinations of records comprising: "BankA + CD
> 123", "BankA + CD ABC", "BankB + CD 123", "BankB + CD ABC", but not
> "BankA + CD 123" twice. Got it? If it helps, my relationships flow
> 1 to many from CD/Bank (one) to AccountCD (many).
>
> *** That is, the way the schema is now, I can enter any combination of
> bank and CD I want, without restriction, even if it's a double: ie.,
> "BankA + CD 123", "BankA + CD 123".
>
> Got Access?

No, not at hand.

> LOL. Access is great for rapid coding at the GUI level--you should
> see what I've come up with in only a few days, but it's kludgey trying
> to debug stuff.
>
> BTW, I notice a tension between not just front end GUI and back end dB
> schema people, but between putting data traps and data crunching code
> in the GUI front end versus writing a SQL query or otherwise dealing
> with the data in the back end. In fact, some people (the posters at
> microsoft . public . access . formscoding and elsewhere) imply and
> state that it's better to deal with this stuff at the front end, since
> SQL "puts too much load onto the servers" (or in particular Access), a
> preformance issue, while others, I suspect you guys, think that the
> front end technique is too much of a maintenance problem or is bad
> design.

It is a matter of focus and scale. Using your editor to edit textfiles with data is one end of the scale. While it is obviously not a good way to protect your data against mistakes, it suffices for a lot of my personal data.

--
What you see depends on where you stand.