Re: Relational Databases and Their Guts

"Marshall Spight" <mspight_at_dnai.com> wrote in message news:LxjJa.1018625$OV.1115113_at_rwcrnsc54...
> "Todd Bandrowsky" <anakin_at_unitedsoftworks.com> wrote in message
news:af3d9224.0306220431.7baeca67_at_posting.google.com...
> > > To my mind, the best reason to complain about SQL not being relational
> > > is that it allows duplicates. NULLs are pretty wacky, too.
> >
> > So, strictly speaking, even though it may not be particularly
> > efficient to implement, instead of a relation of:
> >
> > company relation
> > companyid pk
> > company
> > street1
> > street2
> > city
> > state
> > zip
> >
> > and let street2 be nullable, you would?
> >
> > company relation
> > companyid pk
> > company
> > city
> > state
> > zip
> >
> > company street relation
> > streetid pk
> > companyid pk
> > order
> >
> > street relation
> > streetid
> > streetname
> >
> > to allow a company have multiple streets. This avoids the NULL and
> > represents 0 - N streets that a company might have.
>
> I'm not clear I understand you example. Assuming I understand your
> notation (the first line of each group is the table name, the rest are
> attribute names?) you example is odd. In the real world, a company
> may have many addresses, but it would not have zero or more streets
> independent of city, state, etc. If there is typically an optional "addr2"
> (not "street2") in an address table (not the company table) then it's
> because some addresses have two lines between company name and
> city, and some have only one.
>
> But let me take your question to be simple "how does one deal with
> optional data?" If we imagine a record type with attributes a, b, c, and
> optionally d, then I see three approaches:
>
> 1) table t has attributes (a, b, c, d) and there is a distinguished value
of
> the type of d that represents "no d". For example, a zero length string
> for addr2 would indicate "no address line 2." This is distinctly different
> from null, although it may not seem like an important difference at first.
>
> 2) Declare the column for d to be of a type that consists of all the
values
> of the type of d union a specific value of a specific different type. I
believe
> these are called "sum types" in sum languages (ha ha) such as ML and
> unfortunately, most popular languages don't have this facility, but this
> could be compensated for by the call-level interface to the database.
>
> 3) A second table with two columns: the primary-key-foreign-key of the
> first table, and a d column.
>
> (If anyone is still reading, there is a question I've had about this for a
while:
> couldn't these three logically-equivalent cases be made to be simply
> syntactic differences? There are details to be worked out, but the idea
> intrigues me.)

One can make the above options equivalent using CREATE VIEW.... Received on Sun Jun 22 2003 - 17:18:30 CEST