Re: attribute name conflicts
Date: Thu, 28 Jun 2007 18:29:37 GMT
Message-ID: <l6Tgi.6049$bj5.1256_at_trndny07>
"paul c" <toledobythesea_at_oohay.ac> wrote in message
news:aMQgi.69661$1i1.43867_at_pd7urf3no...
> David Cressey wrote:
> > "paul c" <toledobythesea_at_oohay.ac> wrote in message
> > news:P2Agi.68285$NV3.22266_at_pd7urf2no...
> >
> >>David Cressey wrote:
> >>
> >>>"paul c" <toledobythesea_at_oohay.ac> wrote in message
> >>
> >>...
> >>
> >>>>Oops, sorry about that, I meant to ask: Does it ever make sense within
a
> >>>>given appl'n to have two different relations that use the same
attribute
> >>>>name but with different types?
> >>>>
> >>>>p
> >>>
> >>>
> >>>I'm not sure it makes sense, but I once saw a database where lots of
> >>>different entities had a column with the name "NAME". (I am not making
> >
> > this
> >
> >>>up). NAME might refer to a City's name in one context, and airline's
> >
> > name
> >
> >>>in another context, and an airport's name in a third context.
> >>>These columns all had the same datatype and precision (CHAR(20)) so
> >
> > this
> >
> >>>may not be a case of what you are describing.
> >>
> >>I have too, along with the name "ID", especially in non-relational, or
> >>should I say non-sql db's.
> >>
> >>This reminds me that I've seen the so-called "user types" of some sql
> >>products criticized because they weren't anything more than aliases, but
> >>I sometimes wonder if any products actually allow you to name a type (if
> >>not its values and operators) and say also that for entry purposes it is
> >>of type CHAR{20} but that for identity purposes, eg., when joining, it
> >>is of the named type, eg., "CITYNAME".
> >>
> >
> >
> > I'm not sure, but I think the "user types" you are referring to are the
> > same thing as what I would call "user defined domains". The SQL Syntax
> > would be:
> >
> > CREATE DOMAIN CITYNAME CHAR(20);
> > ...
>
> I think that's what I meant (I often forget to distinguish between the
> SQL use and what people like D&D mean by domain). From what little I've
> been able to find out, it seems that when JOIN is involved, some SQL's
> are quite happy to try to coerce a "CHAR(20)" to, say, an INTEGER.
>
Many SQL implementations will perform datatype conversions when necessary. Sometimes that automatic conversion can have unintended consequences when the data model used to build the database disagrees witht he implicit model the programmers have.
Example:
We have a column named MUMBLE_ID of type NUMBER. It contains values: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 We retrieve MAX(MUMBLE_ID) and we get the value 10, as expected.
Now we have a column named MUMBLE_ID of type VARCHAR. It contains values: '1','2','3','4','5','6','7','8','9','10' We retrieve MAX(MUMBLE_ID) and we get the value '9' which may not be what we expect.
The reason is that MAX is polymorphic. When applied to character strings, it returns the alphabetically last, which is '9', instead of the numerically greatest.
This can raise hell with autoincremented columns if you use the wrong datatype. The RDBMS may keep you from doing the wrong thing if you use the RDBMS to autoincrement, but if you autoincrement under program control you can shoot yourself in the foot. Received on Thu Jun 28 2007 - 20:29:37 CEST