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

On Dec 30, 2:19 pm, mAsterdam <mAster..._at_vrijdag.org> wrote:
> 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.

I think that was Bob's quote, but even he didn't recognize it, so I misunderstood.

>
> > 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?

You can call it Persons if you want; what's in a name?

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

Yes, that's right.

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

Well, a quick check showed that I was wrong (just constructed a simple three table dB with the third table having a compound key from the first two, and when I tried to enter a redundant combination, Access warned me and would not allow it). So Access does allow compound keys and does enforce constraints. So all is well. Access 2003 is quirky, as some metadata apparently resides in the engine that has to be flushed out it seems (even in this simple example I had to open and close a few files to flush out the data). Also my problem may have been I changed the schema as you call it "mid-stream" and some old data was in the dB from a prior schema, and perhaps it caused Access to choke. In any event, for my particular prior problem I programically inserted a data trap to catch redundancies and alert the user, so all is well.

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

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

Yes, that's right. And Access is limited to 30 people using it at one time I think, so the scale is <= 30.

RL Received on Sun Dec 30 2007 - 23:56:06 CET