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

On Dec 30, 10:40 am, mAsterdam <mAster..._at_vrijdag.org> wrote:

>
> --
> What you see depends on where you stand.

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:

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"

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

I do this, but unfortunately in Access I cannot enter a combination of "CD" and "Bank" that is unique. 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?

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.

RL Received on Sun Dec 30 2007 - 18:09:08 CET