Re: Dreaming About Redesigning SQL

Mike Preece wrote:
> andrewst <member14183_at_dbforums.com> wrote:

>>First of all, as Bob pointed out yesterday, the relational model does
>>allow for relation-valued attributes, so that all the phone numbers for
>>a person could be stored within the single person record.  However, that
>>would not be the "traditional" relational approach to this.

There are two reasons why RVA (relation-valued attributes) are not used in the traditional approach.

One is the horribly pragmatic point that neither SQL DBMS nor any RDBMS (possibly excepting Alphora) actually implement support for RVAs, so it is difficult to use in practice what only exists in theory (that sounds familiar - c.d.p should be happy with that). The main reason that RVAs have not been implemented is that they were only recognized as valid in the last 10 years or so, and SQL has been standardized rather longer than that (17 years or so).

The other reason, which might apply even if RVAs were available for use, is that it introduces an asymmetry between the tables whereas the traditional solution preserves that symmetry. Polya said it best in his "How to Solve It!" book - "Try to treat symmetrically what is symmetrical, and do not destroy wantonly any natural symmetry". In this case, there is a considerable bias towards the view that you don't usually need to know who owns a given phone number (but you do need to know the phone numbers of a given person). The traditional relational solution treats those queries substantially the same. Using an RVA, the code for one query is considerably different from the other.

So, if you were sufficiently confident that the RVA design was not over-complicating your queries, there is no reason not to use it if your DBMS supports them. If your DBMS does not support RVAs, then you can't use an approach which depends on them. And one of the beauties of the relational model (which PICK might also claim - I'm not sure) is that if you need to change your mind (so that RVAs are not a good idea after all), then you can probably change the database too without suspending the entire system.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/