Re: A philosophical newbie issue: catch redundant errors via relationships or programmically?
From: rpl <plinnane3_at_yahoo.com.invalid>
Date: Sun, 30 Dec 2007 13:02:14 -0500
Message-ID: <fl8m1k$cs5$1_at_registered.motzarella.org>
>
> 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.
Date: Sun, 30 Dec 2007 13:02:14 -0500
Message-ID: <fl8m1k$cs5$1_at_registered.motzarella.org>
raylopez99 wrote:
> 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.
the "t" in c.d.t. stands for "theory". Experienced carpenters can build quite serviceable toolsheds and doghouses without having to consult or be, an architect. You could probably even build a birdhouse with a Leatherman, Dremel and some determination.
Since I don't particularly understand how what you define as CD's, Banks and Accounts all fit together, and also, like yourself, am too lazy to find out, my off-the-cuff observation that you've buggered it up probably isn't worth much.
rpl Received on Sun Dec 30 2007 - 19:02:14 CET