Re: Dreaming About Redesigning SQL

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

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

Marshall Received on Mon Oct 27 2003 - 08:40:36 CET