Re: Dreaming About Redesigning SQL

"Marshall Spight" <mspight_at_dnai.com> wrote in message news:<UZ3nb.30451$ao4.58659_at_attbi_s51>...
> "Mike Preece" <michael_at_preece.net> wrote in message news:1b0b566c.0310262147.6d153f0f_at_posting.google.com...
> > "Marshall Spight" <mspight_at_dnai.com> wrote in message news:<yZQmb.35715$HS4.125895@attbi_s01>...
> > >
> > > See? The cross-table ids don't match. In the Persons file, 222-2222
> > > belong to Mike; in the PhoneNumbers file, it belong to Marshall.
> > >
> > > Is this possble in Pick, and if so, what, if anything, can be done
> > > about it? (If it's not possible, what did I get wrong?)
> > >
> >
> > You *could* do this - although it's unlikely you'd want to.
>
> The point I want to make is that, (if my example is an accurate
> description of the way it would be done in Pick,) this is a
> source of data corruption. If any app or interactive user update
> one MV list of phone numbers incompatibly with the other
> list, then you've got a contradiction in you database, aka
> corruption.

Yes - not the source of data corruption, but the effect.

>
> > Could you not do it in SQL-relational?
>
> Well, if we declare our schema that way, we could. But we
> have a better way: the join table. This kind of structure
> makes the above kind of data corruption impossible.
>
> I would have a persons table, with a primary key, and a
> phone numbers table with a primary key (possibly the phone
> number itself) and another table with two foreign keys:
> person id and phone id.

>

Oh. You mean like an index? We could just have a single file:

  File Phones
  ID: Phone Number
  001: Phone type ('C'ell, 'H'ome, 'F'ax, 'W'ork, 'D'irect or whatever)   002: PersonalIDs (multivalued)
  ..and any other data relating primarily to the phone

and index it on PersonalID.

I think it's more or less the same - or are b-tree indexes not allowed?

Mike. Received on Mon Oct 27 2003 - 13:30:02 CET