Re: MV and SQL

<michael_at_preece.net> wrote in message news:1137715461.014286.193420_at_o13g2000cwo.googlegroups.com...
> x wrote:
>
> > <michael_at_preece.net> wrote in message
> > news:1137679787.138095.286740_at_g44g2000cwa.googlegroups.com...
> > >
> > > x wrote:
> > >
> > > > <michael_at_preece.net> wrote in message
> > > > news:1137637589.943828.10850_at_z14g2000cwz.googlegroups.com...
> > > > >
> >
> > > >
> > > > Are you sure that a column in Pick is equivalent to a column in an
SQL
> > table
> > > > ?
> > >
> > > Well - not exactly equivalent obviously. Take, for instance, a
> > > ProductNumber field. Without defining it as any particular data type,
> > > you might have been using it for years to store numbers. They can be
> > > pure numerics of a fixed length - or of variable length, it doesn't
> > > matter. You can then start using that same field to store
> > > ProductNumbers with a suffix - as in 12345 & 12345-01 & 12345-02.
Then,
> > > later, the company using this file/table might undergo some change -
as
> > > in a takeover or something, and have a need to include ProductNumbers
> > > of a totally different "type". Say the new company has a similar
> > > product range and you want to include references to those products
> > > using the alternative codes. No problem - just start including product
> > > "numbers" 1234ABC-XYZ-01 in the same "column". Pick - or the Pick
> > > applications using the data - won't bat an eye.
> >
> > With an SQL DBMS you can also change the type from numeric to varchar
and
> > use that.
> > You can also change the type from varchar to array if your SQL DBMS
support
> > (out of the box or by writing an extension) arrays.
> >

I think the discussion between Michael and x has gotten slightly off track. The question of whether the values in a single column are all of the same type or not is ultimately not one of form but of content. I can have data that's of very different types, at the level of meaning, but shares a common format.

In SQL, I could define both zipcodes and social security numbers to be CHAR(9). (The hyphens have been stripped). And, if I wanted to, I could store both kinds of data in a single SQL column. I would need some kind of parallel column to disambiguate, at retrieval time to tell me whether each item was a zip or an SSN.

Of course, this would be an atrocious design, and you would have to expect much confusion among the programmers, followed by lousy performance at the end of the day, if you followed this kind of design.

So the question as far as I'm concerned is not whether all the values in a single Pick column have the same form, but whether they all convey different instances of the same kind of information. And this is not a question about what Pick is capable of. Michael has already addressed that. It's a question about what common practices are. Received on Fri Jan 20 2006 - 06:33:12 CET